Stock Portfolio Tracker (2)
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
Still loading...
ABCDEFGHIJKLMNOPQRST
1
Stock Portfolio Tracker
2
Brought to you by:InvestmentMoats.com
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
NowJuly 21, 2017 04:57:39 +0800
6
DonateIf you appreciate this spreadsheet and would like to donate to the effort, donate here!
7
How to use this
8
Click and read this article which teaches you how to Get Started and Use this Stock Portfolio Tracker
9
Note: To Use do not click SHARE but go to FILE > Make a Copy... to make a copy for yourself
10
Legend
11
Field that needs to be specify by user
12
Field that contains formula. Do not fill in this field with user's own values
13
Version HistoryReference Links
14
1.3Added 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)),"--")

6/19/2017
15
1.21Change Yahoo Last Price column from Vlookup to Index-Filter for performance4/16/2016
16
1.2Added 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
8/22/2015
17
1.13Added 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
5/24/2015
18
1.12Amend 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"))))
19
1.11Removed Mthly Sheet as it is not really very useful
20
1.1Change 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)
21
1.9Reformat Portfolio Summary (13 May 2013)
1) Added Secton to fill in unlisted equities, cash
22
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: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)
23
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
24
1.6-added a Remarks column in Transactions to help users take note of each transactions
25
1.5-added "Yield on Transaction" in Transactions to reflect the yield on individual sell and dividend transaction
26
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
27
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
28
1.2- Added Last Price Yield to Stock Summary to let user compare against yield on cost
29
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
30
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
31
0.3- Added Portfolio Summary Sheet
32
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))))
33
0.1Created first draft of stock portfolio tracker
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
Loading...
 
 
 
Read This First
Portfolio Summary
Portfolio History
Cash Register
Stock Summary
Transactions
Stock Summary USD
Transactions USD
Stock Summary HKD
Transactions HKD
Yrly
Yahoo Data Ref
Yahoo Data Ref USD
Yahoo Data Ref HKD
Ref