ABCDEFGHIJKLMNOPQRST
1
Google Stock Portfolio Tracker
2
Original By:InvestmentMoats.com
3
Edited for US Mkt By:Old School Value
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
5
6
How to Save To Your Account
7
How to use tutorial for this spreadsheet
8
Legend
9
Field that needs to be specified by user in yellow
10
Field that contains formula. Do not fill in this field with user's own values. Blue cells.
11
12
MAKE A COPY TO YOUR ACCOUNT
13
DO NOT request access.
DO NOT REQUEST access.
DO NOT REQUEST ACCESS.
ppl still request access despite the all caps... :(

Just make a copy and save to your account.
14
1. Click "File" -> "Make a Copy"
2. Rename and press OK to save to your account
15
16
Version History
17
OSVFA1.1- Updated charts and legends on Summary
- Cleaned up display by including error checks
- Updated Summary_OSV to auto load unique stocks. No need to manually add stocks to this sheet.
- Updated ChartData to better sort data
18
OSVEA1.1- Cleaned up portfolio summary
- Cleaned up Summary_OSV
- Cleaned up Transactions_OSV
- Updated transactions data template for easier understanding of how to use
- Updated blog post for more detailed instructions
- Updated instructions
19
OSVAA1- Summary: Removed 50 and 200 MA and fixed conditional formatting
- Transactions: Added "fee" in transactions and updated formulas to include Fee
- Transactions: Added realized gain/loss %
- Summary: Added error cleanup on Summary tab
- Stock_Ref: Removed Yahoo data references and use googlefinance function to replace the yahoo stock data info. Sheet hidden.
- Ref: Changed stock categories and added transaction list. Sheet hidden.
- ChartData: Worksheet that sorts the Summary for charting. Do not change.
- Remove original chart and replace with new charts on Portfolio Summary
20
1.2- Added Last Price Yield to Stock Summary to let user compare against yield on cost
21
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
22
1- 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
23
0.3- Added Portfolio Summary Sheet
24
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))))
25
0.1Created first draft of stock portfolio tracker
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