Example of portfolio tracking in Google Sheets (more than 4 accounts)
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
1. Insert the following data (in blue bold) for each stock purchase on the "current holdings" tab
2
3
Account
Use a short description of the account. Make sure you spell it the same each time and don't add spaces or extra characters.
4
Symbol
Stock symbol. You know something is wrong if the "current stock price" column doesn't populate
5
Pur. Price
Purchase price per share
6
Pur. Date
Date the stock was purchased
7
Shares
Number of shares purchased
8
Cost Basis
Calculated column (Purchase price x Shares)
9
Curr Stock Price
Google populated column
10
Curr Tot Value
Calculated column
11
Gain(Loss)
Calculated column
12
Gain %
Calculated column
13
Yrs Held
Calculated column
14
% Gain/yr
Calculated column
15
Vs S&P
Calculated column which represents the % of your gain vs. the S&P over the same time period
16
S&P Cost basis
Google populated column which represents the value at close for the S&P the day you bought your stock
17
S&P % Gain
Calculated column representing the % gain for the S&P over the same time period since you bought your stock
18
S&P $ Gain(Loss)
Calculated column representing the $ gain/loss for the S&P over the same time period if you bought the S&P instead of your stock
19
Yest $
Calculated column reflecting the value of this position at yesterday's close
20
Chg from yest $
Calculated column reflecting the change in value of this position from yesterday's close
21
10/31/2019
Calculated column reflecting the total value based on the date from cell Q2 on the stock summary page
22
Chg from <-- $
Calculated column reflecting the change in total value based from the date from cell Q2 on the stock summary page (note does not include positions not yet purchased on date)
23
Int Years
Calculated column reflecting the integer number of years the position is held
24
Yrs Held
Calculated column reflecting the grouping of years held based on table on the Port by age hidden in column M&N
25
26
27
1a. If you getting items from Motley Fool's Scorecard tool. Go to your "stocks transaction view" (like this picture)
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Highlight the cells you want starting from the bottom with the last stock in the Total value "cell" and select up and left to "ticker"
43
Copy
44
and Paste into a new tab in google sheets. (it should look like Example dump from scorecard tab)
45
Sort by ticker and eliminate stocks you no longer own (buy and sell transactions)
46
Eliminate any other unwanted transactions (you only want to have buys of stocks you currently still hold)
47
If you have "cash in" transactions, you might want to save those to another sheet. (Currently, the spreadsheet can't handle cash)
48
Adjust data in rows for partial sells (i.e. bought 100 shares, only own 50 now)
49
Once you are satisified your list only contains buys for stocks you currently hold
50
Delete the "type" column from your scorecard data
51
copy Ticker, Trade date, shares and price per share data to the "current holdings" tab just below my data like this
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
Fill in the your account name for each holding, then highlight the data from columns F through P on the last row with data in it and "grab" the little blue box in the bottom right and drag it down to match your buys.
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
2. Check to see if the Summary in dollars tabs is summarizing appropriately
94
The four pivot table tabs (left to right) on the "Dollars Summary" tabs are
95
1. Total current value (by stock in the rows, and accounts in the columns)
96
2. Total Gain(Loss) value of all stocks
97
3. Cost basis value
98
4. S&P dollar gain (represents the gain/loss if you have bought the S&P instead of your stock
99
100
3. Once you've copied the data from columns F through P to your data, delete the rows containing my sample data. Your buys should now start in row 3.
Loading...