I am new to google app script and trying to learn as much as I can but I have stumble against something which is over my head.....
My aim is to create a function, which calculates the realized gains of a stock, given the input data of the transactional history, in the accounting style "FIFO", which stands for first in, first out.
The formula for calculating realized gains is relatively straight forward
(Total Sell price - Original Cost ) = Realized gains/loss
Realized Gains/Loss / Original Cost = Profit/loss (%)
If someone has bought a stock at three different times for three different prices (x1,x2,x3) and you wish to calculate the 'realized gains' - do you take the original cost of x1, x2 or x3? FIFO style would take X1, as it is 'first in, first out'. This is important because in the US there is something called capital gain tax based on all realized gains.
Here is my data: https://docs.google.com/spreadsheets/d/1V7CpCxBH0lg6wi1TAhfZJP5gXE8hj7ivQ8_ULxLSLgs/edit?usp=sharing
const ss = SpreadsheetApp.getActiveSpreadsheet();
const historySheet = ss.getSheetByName('Blad1');
function fifoProject () {
let input = historySheet.getDataRange().getValues();
let onlyBuyRows = input.filter(row => row[2] == 'Buy');
let roundedTotal, priceQuantityMultiplication;
let onlyColABDF = onlyBuyRows.map(row => {
roundedTotal = Math.round(row[5] * 100) / 100;
priceQuantityMultiplication = row[3] * roundedTotal;
return [
row[0], row[1], row[3], roundedTotal, priceQuantityMultiplication
]});
let sorted = onlyColABDF.sort((a, b) => {
if (a[1] > b[1]) return 1;
if (a[1] < b[1]) return -1;
if (a[0] > b[0]) return 1;
if (a[0] < b[0]) return -1;
return 0;
});
let arrayBuyData = [];
arrayBuyData.push(sorted);
console.log(arrayBuyData);
//ss.getSheetByName('output').getRange(1, 1, sorted.length, sorted[0].length).setValues(sorted)
let input2 = historySheet.getRange(2, 1, historySheet.getLastRow() - 1, 4).getValues();
let onlySellRows = input2.filter(row => row[2] == 'Sell');
let sellTotalArray = []
let addAllSellObject = onlySellRows.reduce((acc, curr) => {
if (curr[1] in acc) {
acc[curr[1]] += curr[3]
} else {
acc[curr[1]] = curr[3]
}
return acc;
}, {});
let addAllSellArray = Object.entries(addAllSellObject).sort((a, b) => {
if (a[0] > b[0]) return 1;
if (a[0] < b[0]) return -1;
return 0;
})
sellTotalArray.push(addAllSellArray);
console.log(sellTotalArray[0][1]);
}
Here is what I think the function should do:
Any idea's in the right direction would be very welcome.
EDIT: Trying to clarify the question. Wish to do this:
<style>
.demo {
border:1px solid #C0C0C0;
border-collapse:collapse;
padding:5px;
}
.demo th {
border:1px solid #C0C0C0;
padding:5px;
background:#F0F0F0;
}
.demo td {
border:1px solid #C0C0C0;
padding:5px;
}
</style>
<table class="demo">
<caption></caption> <thead> <tr> <th>Date</th>
<th>Type</th>
<th>Amount p.p</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
<tr>
<td> Jan</td>
<td> Buy</td>
<td> $10</td>
<td> 5</td>
</tr>
<tr>
<td> Feb</td>
<td> Buy</td>
<td> $15</td>
<td> 8</td>
</tr>
</tbody>
</table>For example if then 7 quantity sold for $23, it would result in:
<style>
.demo {
border:1px solid #C0C0C0;
border-collapse:collapse;
padding:5px;
}
.demo th {
border:1px solid #C0C0C0;
padding:5px;
background:#F0F0F0;
}
.demo td {
border:1px solid #C0C0C0;
padding:5px;
}
</style>
<table class="demo">
<caption></caption> <thead> <tr> <th>Date</th>
<th>Amount</th>
<th>Buy price p.p</th>
<th>Profit p.p</th>
</tr> </thead> <tbody> <tr>
<td> Jan</td>
<td> 5</td>
<td> $10</td>
<td> $13</td>
</tr>
<tr>
<td> Feb</td>
<td> 2</td>
<td> $15</td>
<td> $8</td>
</tr>
</tbody>
</table>TOTALS:
<style>
.demo {
border:1px solid #C0C0C0;
border-collapse:collapse;
padding:5px;
}
.demo th {
border:1px solid #C0C0C0;
padding:5px;
background:#F0F0F0;
}
.demo td {
border:1px solid #C0C0C0;
padding:5px;
}
</style>
<table class="demo">
<caption></caption> <thead> <tr> <th><br></th>
<th><br></th>
</tr>
</thead>
<tbody>
<tr>
<td> Total buy price</td>
<td> $80</td>
</tr>
<tr>
<td> Total sell price</td>
<td> $161</td>
</tr>
<tr>
<td> Total profit (or loss)</td>
<td> $81</td>
</tr>
<tbody>
</table>EDIT 2: Based on the answer of Jacques, I have created the following code:
const ss = SpreadsheetApp.getActiveSpreadsheet();
const historySheet = ss.getSheetByName('Blad1');
// Function which pushes item x amount of times based on a number
function pushNumber (number, array, item) {
for(var i = 0; i<number; i++){
array.push(item);
}
}
function shiftNumber (number, array, item) {
for(var i = 0; i<number; i++){
array.shift(item);
}
}
function projectFIFO () {
let input = historySheet.getRange(3,1,historySheet.getLastRow()-1, 5).getValues();
for (var i = 0; i<security.length; i++) {
let action = row[2].toString();
let quantity = Number(row[3]);
let price = Number(row[4]);
let emptyArray = [];
// console.log(quantity);
for (security of array)
if (action === 'Buy') {
let numberPush = quantity;
pushNumber(quantity, emptyArray, price);
};
console.log(emptyArray);
};
}
Basically I have created a helperfunction pushNumber, which pushes based on the amount of Number. Then in the fifo function I create a for loop to go through each row and see if there is a buy and push the buy based on the amount of quantity, the push is inside an empty array. However, I would like to learn how to do this for every security seperatly instead of the entire range. I think perhaps an object is a solution but haven't figured it out yet.
After studying your code and the example Sheet I see that you are very close to accomplishing your goal. You only have to code a function that interacts with your Transactions table in order to get the data for your desired results table.
In the first interaction, filter the Transaction table to only show your desired ticket (as you did in the example script). Proceed to interact the table from the top to the bottom (you can use the getRange()/getValues() as shown in your example). Now, for every Buy row, push() the price as many times as shares bought. So for example if you bought 3 shares at $313, you should push 313, 313, 313. For every Sell order you only have to shift() the array and calculate the difference between the returned value (buying price) and the selling price.
With that approach you are going to effectively develop a FIFO logic that manages the buying and selling orders, so the difference between them will be the realized profit. Feel free to leave a comment if you have doubts about this approach.
You can use this approach to remove duplicates from an array. This would be useful if you don't know the names of the securities beforehand.
var allTickers = ['AAPL', 'AAPL', 'GOOG', 'MSFT', '2222.SR', 'MSFT', 'AMZN',
'AAPL', '2222.SR', 'MSFT', 'AMZN', '2222.SR'
]
var uniqueTickers = [...new Set(allTickers)];