| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Stock Portfolio Tracker | |||||||||||||||||||
2 | Brought to you by: | InvestmentMoats.com | ||||||||||||||||||
3 | Please send your feedbacks and queries to | Queries and Feedback | ||||||||||||||||||
4 | 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 | ||||||||||||||||||
5 | Donate | If you appreciate this spreadsheet and would like to donate to the effort, donate here! | ||||||||||||||||||
6 | How to use this | |||||||||||||||||||
7 | Detail how to use instructions view this article | |||||||||||||||||||
8 | Legend | |||||||||||||||||||
9 | Field that needs to be specify by user | |||||||||||||||||||
10 | Field that contains formula. Do not fill in this field with user's own values | |||||||||||||||||||
11 | Version History | Reference Links | ||||||||||||||||||
12 | 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")))) | |||||||||||||||||
13 | 1.11 | Removed Mthly Sheet as it is not really very useful | ||||||||||||||||||
14 | 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: =iferror(if(row()<>2,INDEX(arrayformula(filter($J1:$J$2,$C1:$C$2<>"",row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ;1),0),0) Previous Cost Formula: =iferror(if(row()<>2,INDEX(arrayformula(filter($O1:$O$2,$C1:$C$2<>"",row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ;1),0),0) | |||||||||||||||||
15 | 1.9 | Reformat Portfolio Summary (13 May 2013) 1) Added Secton to fill in unlisted equities, cash | ||||||||||||||||||
16 | 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:Q,"SELECT Q WHERE (B = 'Buy' OR B = 'Sell' OR B='Div') AND C = '" & B2 & "'"), U2, "," ) , ",") , SPLIT ( JOIN ("," , QUERY(Transactions!A$2:Q,"SELECT A WHERE( B = 'Buy' OR B = 'Sell' OR B='Div') AND C = '" & B2 & "'"), TODAY(), "," ) , ",") ),0) | |||||||||||||||||
17 | 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 | ||||||||||||||||||
18 | 1.6 | -added a Remarks column in Transactions to help users take note of each transactions | ||||||||||||||||||
19 | 1.5 | -added "Yield on Transaction" in Transactions to reflect the yield on individual sell and dividend transaction | ||||||||||||||||||
20 | 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 | |||||||||||||||||
21 | 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 | ||||||||||||||||||
22 | 1.2 | - Added Last Price Yield to Stock Summary to let user compare against yield on cost | ||||||||||||||||||
23 | 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 | ||||||||||||||||||
24 | 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 | ||||||||||||||||||
25 | 0.3 | - Added Portfolio Summary Sheet | ||||||||||||||||||
26 | 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)))) | ||||||||||||||||||
27 | 0.1 | Created first draft of stock portfolio tracker | ||||||||||||||||||
28 | ||||||||||||||||||||
29 | ||||||||||||||||||||
30 | ||||||||||||||||||||
31 | ||||||||||||||||||||
32 | ||||||||||||||||||||
33 | ||||||||||||||||||||
34 | ||||||||||||||||||||
35 | ||||||||||||||||||||
36 | ||||||||||||||||||||
37 | ||||||||||||||||||||
38 | ||||||||||||||||||||
39 | ||||||||||||||||||||
40 | ||||||||||||||||||||
41 | ||||||||||||||||||||
42 | ||||||||||||||||||||
43 | ||||||||||||||||||||
44 | ||||||||||||||||||||
45 | ||||||||||||||||||||
46 | ||||||||||||||||||||
47 | ||||||||||||||||||||
48 | ||||||||||||||||||||
49 | ||||||||||||||||||||
50 | ||||||||||||||||||||
51 | ||||||||||||||||||||
52 | ||||||||||||||||||||
53 | ||||||||||||||||||||
54 | ||||||||||||||||||||
55 | ||||||||||||||||||||
56 | ||||||||||||||||||||
57 | ||||||||||||||||||||
58 | ||||||||||||||||||||
59 | ||||||||||||||||||||
60 | ||||||||||||||||||||
61 | ||||||||||||||||||||
62 | ||||||||||||||||||||
63 | ||||||||||||||||||||
64 | ||||||||||||||||||||
65 | ||||||||||||||||||||
66 | ||||||||||||||||||||
67 | ||||||||||||||||||||
68 | ||||||||||||||||||||
69 | ||||||||||||||||||||
70 | ||||||||||||||||||||
71 | ||||||||||||||||||||
72 | ||||||||||||||||||||
73 | ||||||||||||||||||||
74 | ||||||||||||||||||||
75 | ||||||||||||||||||||
76 | ||||||||||||||||||||
77 | ||||||||||||||||||||
78 | ||||||||||||||||||||
79 | ||||||||||||||||||||
80 | ||||||||||||||||||||
81 | ||||||||||||||||||||
82 | ||||||||||||||||||||
83 | ||||||||||||||||||||
84 | ||||||||||||||||||||
85 | ||||||||||||||||||||
86 | ||||||||||||||||||||
87 | ||||||||||||||||||||
88 | ||||||||||||||||||||
89 | ||||||||||||||||||||
90 | ||||||||||||||||||||
91 | ||||||||||||||||||||
92 | ||||||||||||||||||||
93 | ||||||||||||||||||||
94 | ||||||||||||||||||||
95 | ||||||||||||||||||||
96 | ||||||||||||||||||||
97 | ||||||||||||||||||||
98 | ||||||||||||||||||||
99 | ||||||||||||||||||||
100 | ||||||||||||||||||||