ABCDEFGHIJKLMNOPQRST
1
Stock Portfolio Tracker
2
Brought to you by:InvestmentMoats.comHeavily customized by pjigar
3
Please send your feedbacks and queries toQueries and Feedback
4
DescriptionStock 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
DonateIf 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 HistoryReference Links
12
1.9Reformat Portfolio Summary (13 May 2013)
1) Added Secton to fill in unlisted equities, cash
13
1.8Take 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)
14
1.7Functionality - 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
15
1.6-added a Remarks column in Transactions to help users take note of each transactions
16
1.5-added "Yield on Transaction" in Transactions to reflect the yield on individual sell and dividend transaction
17
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
18
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
19
1.2- Added Last Price Yield to Stock Summary to let user compare against yield on cost
20
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
21
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
22
0.3- Added Portfolio Summary Sheet
23
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))))
24
0.1Created first draft of stock portfolio tracker
25
26
27
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