Last active
March 27, 2021 15:44
-
-
Save MEN8v/9cd7fc9e0bab03606b8d16ec21c583af to your computer and use it in GitHub Desktop.
This Google Apps script will generate a list of your stock holdings for up to 6 accounts taking a list of transactions as input
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //Create a map to store unique tickers for an account | |
| var account1 = new Map(); | |
| var account2 = new Map(); | |
| var account3 = new Map(); | |
| var account4 = new Map(); | |
| var account5 = new Map(); | |
| var account6 = new Map(); | |
| function generateTrade(account, stockQuant, stockPrice, action) { | |
| return { | |
| account: account, | |
| shares: stockQuant, | |
| price: stockPrice, | |
| action: action | |
| }; | |
| } | |
| function generateFifo (security, accounts, actions, quantity, price) { | |
| var uniqueAccounts = []; | |
| for (var j = 0; j < security.length; j++) { | |
| originalAccount = accounts[j].toString(); | |
| var addToUniqueAccounts = 1; | |
| for (var c = 0; c < uniqueAccounts.length; c++) { | |
| if (uniqueAccounts[c] == originalAccount) { | |
| addToUniqueAccounts = 0; | |
| uniqueAccounts.length = c; | |
| } | |
| } | |
| uniqueAccounts.push(originalAccount); | |
| } | |
| Logger.log(uniqueAccounts); | |
| for(var i = 0; i < security.length; i++){ | |
| let ticker = security[i].toString(); | |
| let account = accounts[i].toString(); | |
| let action = actions[i].toString(); | |
| let stockQuant = Number(quantity[i]); | |
| let stockPrice = Number(price[i]); | |
| let trade = generateTrade(account, stockQuant, stockPrice, action); | |
| if (action == "Buy" || action.toUpperCase() == "DRIP" || action == "Split") { | |
| if (account == uniqueAccounts[0]) { | |
| let activeTrades = account1.get(ticker); | |
| if (activeTrades == null) { | |
| account1.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } else if (account == uniqueAccounts[1]) { | |
| let activeTrades = account2.get(ticker); | |
| if (activeTrades == null) { | |
| account2.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } else if (account == uniqueAccounts[2]) { | |
| let activeTrades = account3.get(ticker); | |
| if (activeTrades == null) { | |
| account3.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } else if (account == uniqueAccounts[3]) { | |
| let activeTrades = account4.get(ticker); | |
| if (activeTrades == null) { | |
| account4.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } else if (account == uniqueAccounts[4]) { | |
| let activeTrades = account5.get(ticker); | |
| if (activeTrades == null) { | |
| account5.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } else { | |
| let activeTrades = account6.get(ticker); | |
| if (activeTrades == null) { | |
| account6.set(ticker, [trade]); | |
| } else { | |
| activeTrades.push(trade); | |
| } | |
| } | |
| } | |
| if(action == "Sell"){ | |
| if (account == uniqueAccounts[0]) { | |
| let activeTrades = account1.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account1.delete(ticker); | |
| } | |
| } | |
| } else if (account == uniqueAccounts[1]) { | |
| let activeTrades = account2.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account2.delete(ticker); | |
| } | |
| } | |
| }else if (account == uniqueAccounts[2]) { | |
| let activeTrades = account3.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account3.delete(ticker); | |
| } | |
| } | |
| } else if (account == uniqueAccounts[3]) { | |
| let activeTrades = account4.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account4.delete(ticker); | |
| } | |
| } | |
| } else if (account == uniqueAccounts[4]) { | |
| let activeTrades = account5.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account5.delete(ticker); | |
| } | |
| } | |
| } else { | |
| let activeTrades = account6.get(ticker); | |
| let precision = 5; | |
| if(activeTrades != null) { | |
| let sharesToSell = Number(Number(trade.shares).toFixed(precision)); | |
| while (sharesToSell > 0) { | |
| sharesToSell = Number(Number(sharesToSell).toFixed(precision)); | |
| if(activeTrades.length == 0) { | |
| break; | |
| } | |
| if(activeTrades.length > 0){ | |
| let itemToSell = activeTrades[0]; | |
| itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision)); | |
| if(itemToSell.shares == sharesToSell){ | |
| sharesToSell = 0; | |
| activeTrades.splice(0, 1); | |
| } | |
| else if(itemToSell.shares < sharesToSell){ | |
| sharesToSell -= itemToSell.shares; | |
| activeTrades.splice(0, 1); | |
| } | |
| else { | |
| itemToSell.shares -= sharesToSell; | |
| sharesToSell = 0; | |
| } | |
| } | |
| } | |
| if(activeTrades.length == 0){ | |
| account6.delete(ticker); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| /** | |
| * Generates holdings by account, including # of shares and avg price | |
| * @param {History!B3:B} security column. | |
| * @param {History!C3:C} accounts column. | |
| * @param {History!D3:D} actions column. | |
| * @param {History!E3:E} quantity column. | |
| * @param {History!F3:F} price column. | |
| * @customfunction | |
| */ | |
| function myPositions(security, accounts, actions, quantity, price){ | |
| generateFifo(security, accounts, actions, quantity, price); | |
| let returnArray = []; | |
| account1.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| account2.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| account3.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| account4.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| account5.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| account6.forEach((value, key) => { | |
| let shares = 0; | |
| let totalCost = 0; | |
| let avgPrice = 0; | |
| let account = ""; | |
| value.map(trade => { | |
| shares += trade.shares; | |
| account = trade.account | |
| totalCost += trade.shares * trade.price; | |
| }); | |
| avgPrice = totalCost / shares; | |
| returnArray.push([key, account, shares, avgPrice]); | |
| }); | |
| return returnArray.sort(); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment