|Stock Portfolio Tracker|
|Brought to you by:||InvestmentMoats.com|
|Please send your feedbacks and queries to||Queries and Feedback|
|Description||Stock Portfolio Tracker is Investment Moats attempt at creating an online spread sheet that will let a stock investor|
1) Monitor your stocks in a portfolio
2) Track stock purchase by transactions
3) Transactions compatible includes purchase, sale, dividend and stock splits
4) Aggregate transactions to individual stocks
5) Aggregate individual stocks to portfolio
6) Calculate each stock's XIRR
7) Factors in Bonus, Rights Issue
|Now||January 16, 2018 11:46:35 +0800|
|Donate||If you appreciate this spreadsheet and would like to donate to the effort, donate here!|
|How to use this|
|Click and read this article which teaches you how to Get Started and Use this Stock Portfolio Tracker|
|Note: To Use do not click SHARE but go to FILE > Make a Copy... to make a copy for yourself|
|Field that needs to be specify by user|
|Field that contains formula. Do not fill in this field with user's own values|
|Version History||Reference Links|
|1.4||Yahoo Finance API Seems to be taken down after being bought by Verizon. I did a workaround where I would upload and process SGX end of day data, and convert into a CSV|
You can linked this CSV using import data.
At "Yahoo Data Ref", Replace =arrayformula(ImportData("https://download.finance.yahoo.com/d/quotes.csv?s="&LEFT(CONCATenate('Stock Summary'!F2:F),LEN(CONCATenate('Stock Summary'!F2:F))-1)&"&f=snp2l1jkm3m4"&"&"&'Read This First'!$B$5))
With =arrayformula(ImportData("http://investmentmoats.com/shoyo/sp.csv?&"&'Read This First'!$B$5))
|1.3||Added getYahooFinanceData(string) to Script Editor.Change the way stock data is retrieved from Yahoo. Implemented getYahooFinanceData(string) in YahooDataRef, YahooDataRef USD, YahooDataRef HKD|
- also added a "--" in front of index, to force convert text to numbers in Yahoo Price in Stock Summary, Stock Summary USD, Stock Summary HKD
|=iferror(--index(FILTER('Yahoo Data Ref'!D$2:D,'Yahoo Data Ref'!A$2:A=E2)),"--")|
|1.21||Change Yahoo Last Price column from Vlookup to Index-Filter for performance||4/16/2016|
|1.2||Added Cash Register Sheet to help keep track of cash portion. The Cash Register sheet adds up to the Cash Register section of Stock Summary sheet.|
- also added Money Flow, Sub Money Flow, Currency Named List in Ref
|1.13||Added Capital Reduction to Transactions|
- added "CapReduct" type as Transaction Type
- insert if(B2="CapReduct",I2,0) into Cumulative units
- insert if(B2="CapReduct",L2-K2,"Error") into Cumulative costs
|1.12||Amend Transactions, Transactions USD and Transactions HKD. Under Cumulative Cost, if Previous Cost is <= 0, it will not show "ERROR. NO PREVIOUS UNITS". It will show 0.|
This is to cater to the cases, where a user highlighted that in some cases shares can be purchase at zero cost due to it being gifts
|Previous Cumulative cost formula:|
=if(B2="Buy",L2+K2,if(B2="Div",L2,if(B2="Sell",if(L2<=0,"Error.No Previous units.",L2-M2),if(B2="Split",L2,"Error"))))
|1.11||Removed Mthly Sheet as it is not really very useful|
|1.1||Change the formula for Transactions, Transactions USD, Transactions HKD to one which i feel is less complex so that the spreadsheet will not be bogged down as data becomes larger.|
We changed the "Previous Units" and "Previous Costs" Columns in each of the three sheets. We also add a "Prev Row" to them. Previous Units and Costs now computes based on Prev Row, which is the last row of the unique stock, e.g China Merchant Pacific, previous row is XXX
1) Add a column to the left of "Previous Units" for the three sheets
2) Name the column "Prev Row"
3) In row 2 of Prev Row, append this function in (without the " "): "=ARRAYFORMULA(max(if($C1:$C$1=C2,row($C1:$C$1))))"
4) Drag row 2 all the way till the last row
5) Under "Previous Units", in row 2, change the function to:"=if(H2=0,0,INDIRECT("J"&H2))"
6) Drag row 2 all the way till the last row
7) Under "Previous Costs", in row 2, change the function to:"=if(H2=0,0,INDIRECT("O"&H2))"
8) Drag row 2 all the way till the last row
9) Verify that the change in formula did not cause miscalculations
10) Repeat the following for "Transactions USD" and "Transactions HKD" if you are using them
|Previous Units Formula:|
Previous Cost Formula:
|1.9||Reformat Portfolio Summary (13 May 2013)|
1) Added Secton to fill in unlisted equities, cash
|1.8||Take out XIRR formula for individual stocks in Stock Summary and Stock Summary USD because it is too intensive! Specifically, QUERY and VLOOKUP are intensive formulas|
Formula is at right side.
For users who are interested in implementing XIRR on individual stocks can
1) at Stock Summary or Stock Summary USD, create a column after column U (Market Value).
2) copy the formula to the right in Reference Links into the first cell (cell row 2)
3) drag the row 2 downwards to propagate to the rest of the other rows.
|iferror(XIRR( SPLIT ( JOIN ("&" , QUERY(Transactions!A$2:R,"SELECT R WHERE (B = 'Buy' OR B = 'Sell' OR B='Div') AND C = '" & B2 & "'"), U2, "&" ) , "&") , SPLIT ( JOIN ("&" , QUERY(Transactions!A$2:R,"SELECT A WHERE( B = 'Buy' OR B = 'Sell' OR B='Div') AND C = '" & B2 & "'"), TODAY(), "&" ) , "&") ),0)|
|1.7||Functionality - XIRR for individual stocks at stock summary|
- added column Cash Flow to Transactions and Transactions USD
- added column XIRR to Stock Summary and Stock Summary USD
Note: XIRR takes the following example. Buy transactions are negative, Div and Sell are positive. Div is taken into account assuming there are no reinvestment
|1.6||-added a Remarks column in Transactions to help users take note of each transactions|
|1.5||-added "Yield on Transaction" in Transactions to reflect the yield on individual sell and dividend transaction|
|1.4||- Fixed bug in Mthly and Yrly worksheet where #NUM was shown in values that is working previously. What it boils down to is that a bug with MONTH() and YEAR() has been apparently fixed, whereby if these functions were referencing a blank cell, they would return 12 and 1899 respectively, as if the cell they were referencing was actually zero. Now it seems they are (correctly) returning a #NUM! error when this is done.|
-The solution is to append an IFERROR(YEAR(Transactions!$A$2:$A)=$A2)) around the YEAR and the MONTH formulas. If you have taken the sheet before this please do the update accordingly
|Yearly and Monthly Dividends Report Fixed|
|1.3||- Added Mthly worksheet that profiles dividends and stock gains/losses by month so that you can review|
- Added Yrly worksheet that profiles dividends and stock gains/losses by year so that you can review
|1.2||- Added Last Price Yield to Stock Summary to let user compare against yield on cost|
|1.1||-Added Yahoo Data Ref Sheet. This is to store data pull from Yahoo Finance. This is because importData function is limited to 50 times per sheet|
-Data is thus pulled via VLookup in Stock Summary from Yahoo Data Ref
|1.0||- Added Total Gain/Loss to Stock Summary|
- Added Last Price Automation by getting prices from yahoo finance using yahoo CSV file and importdata function http://www.gummy-stuff.org/Yahoo-data.htm
|0.3||- Added Portfolio Summary Sheet|
|0.2||- Added Stock Split.|
- Add Split Ratio column.
- Change Data Validation for B column in Stock Summary to include "Split"
- Add Conditional Formating Rule for B column to include "Split"
- Add Split formula to Cumulative Cost if(B2="Split",K2,"Error")
- Change Cumulative Stock formula. Retiring arrayformula(sumproduct(($C$2:$C2=C2)*($B$2:$B2="Buy")*($D$2:$D2))-sumproduct(($C$2:$C2=C2)*($B$2:$B2="Sell")*($D$2:$D2))
- New Cumulative Stock formula. =if(B2="Buy",H2+D2,if(B2="Sell",H2-D2,if(B2="Div",H2,if(B2="Split",H2*G2,0))))
|0.1||Created first draft of stock portfolio tracker|