May 26th, 2018 | Posted in Tools
Get the Investment Portfolio Tracker file in Google Sheets (log in to your google account, and then click File –> Make a Copy to create your own version). This spreadsheet — like everything else on this site — is completely free, and will always remain so.
[The latest update is now Version 8.5 — published on June 4th, 2021, with some modifications to the script on the Monthly Performance tab to make the calculations more robust. If you’re using an older version and would like to upgrade, just open up a fresh version of the sheet, and paste in the info from your old “setup” and “trade log” tabs into the new version. Voila!]
[Previous versions have added features such as: letting you to specify a custom benchmark and see how your portfolio performance compares (many thanks to Alessandro Coscia for building this!), and the additions of the “Monthly Performance”, “Dividends”, and “Realized Gains” tabs.]
Welcome to your new portfolio tracker — the latest, greatest, and only tool you need to monitor your investment portfolio.
All investors — from the index couch potatoes, to the dividend aficionados, to the active day traders — need a tool to track their investment portfolios.
To measure your progress against your financial goals, you’ll need to have a strong grasp on how much money you have, where it’s invested, and how those investments have been performing.
This spreadsheet is easy to use but still comes packed with powerful features:
- Uses Google Finance formulas to automatically pull the current share price of your investments daily
- Calculates the $ and % return of your portfolio between any range of dates
- Comparison of your portfolio’s performance versus the S&P 500 index and any custom benchmark of your choice
- Dividend tracking
- Reporting on realized gains (super helpful during tax time!)
- Flexibility to group your investments into custom categories (e.g., domestic stocks, international stocks, bonds, etc.)
- Tracks assets in multiple countries, and converts all values into one common currency
- Automated re-balancing calculations versus your target asset allocation
- Supports up to 250 stock tickers, 10 investment accounts, 25 investment categories, and 5 currencies
And did I mention that this all comes free? Say goodbye to monthly subscriptions fees, paid premium features, and all that jazz.
Since I published this spreadsheet back in May 2018, thousands of DIY investors from all around the globe have used the tool, leaving comments such as…
“Thanks so much for sharing this. Took me an afternoon to rebuild 4 years of trading history over multiple accounts in different countries. This is really a great tool.”
“Awesome spreadsheet! This is exactly what I’ve been looking for months now!”
“This an amazing spreadsheet and by far the best I have come across so far. I can’t imagine the amount of effort that must have gone into making this so sophisticated.”
I hope that you’ll feel the same way 🙂
Down below, we’ll do a detailed walk-through of the features, along with some tips on how to use the investment portfolio tracker.
Table of Contents
1) Spreadsheet Instruction Manual
- Setting Up The Spreadsheet
- Trade Log
- Your Portfolio Dashboard
- Monthly Performance Tracking
- Your Portfolio Holdings
- Dividend Tracking
- Realized Gains
- Re-Balancing
2) Tips & Tricks
Setting Up the Investment Portfolio Tracker [back to top]
If you haven’t done so already, open the investment portfolio tracker file in Google Sheets (log in to your google account, and then click File –> Make a Copy to create your own version).
First up, use the “Setup” tab to customize this spreadsheet for your own investment portfolio.
This is where you can set:
- Your investment categories (e.g., domestic equity, international equity, bonds, REIT)
- Your investment accounts (TFSAs / RRSPs for Canadians, 401ks / IRAs for Americans, taxable accounts, and so on)
- The individual ticker symbols for the stocks and ETFs that make up your portfolio (AAPL, VTSAX, VXC, etc…)
A note for investors outside of the US: It’s a best practice to enter your stock tickers including the stock exchange symbol in front of it. Sometimes there is a different company in the US which has the same ticker, in which case google finance will pull the incorrect price. For example, for Bank of Nova Scotia (which is traded on the Toronto Stock Exchange), input the ticker TSE:BNS instead of just BNS (which is traded on the New York Stock Exchange).
To find the precise code to use, just use the search bar on google finance and copy the full ticker that’s shown there.
The Trade Log [back to top]
Once you’re finished with the Setup tab, move on to the Trade Log tab, where you’ll input your history of trades — with each transaction in a separate row.
Let’s take the example of an investor who buys shares of Microsoft (MSFT ticker) over time, then sells a portion of their holdings, and then receives a dividend.
This is what their Trade log tab might look like:
This investor bought 30 shares of MSFT in total, over three separate transactions (10 + 15 + 5).
Then, 12 shares were sold on August 21st for $213 per share. This leaves 18 shares remaining.
As shown on the last row, on September 10th a total dividend payment of $9.18 was received. This is broken down as 18 shares held, with a dividend of $0.51 per share paid.
When you’re entering dividend transactions, only the total $ amount is required ($9.18 in the example) — inputting the quantity of units and amount per unit is optional (18 shares held and $0.51 dividend per share in the example above).
A quick note on tracking cash positions and cash contributions: this tool was built to track your investments (shares held for stocks, ETFs, bonds, etc.), and doesn’t do any cash tracking by default.
Therefore, your trade log only needs to reflect when you buy/sell shares or receive dividends. No inputs are needed for when you add cash or take cash out of your account.
That being said, if you’d like to track your cash balances, please see below in the FAQ section about the $CASH workaround.
Inputting your history of trades into the Trade Log requires time and manual effort, but the reward and insights that you’ll get are just over the horizon!
I’ve got 8 years of trading history in my personal spreadsheet, and now that I’ve put in the work upfront, it just takes a few minutes per month to log new trades and dividends.
Your Portfolio Dashboard [back to top]
The spreadsheet will now handle the heavy lifting!
On the “Dashboard” tab, you’ll be presented with a summary of your portfolio.
At the top of this tab, you’ll find a few drop-down menus which control the outputs of the sheet.
The date cell (C2) determines the date at which your portfolio will be valued. By default, the sheet auto-updates for today’s value, but you can modify this to view your portfolio at at any other date in the past (e.g., at the end of last month or last year).
The currency cell (C3) tells the spreadsheet what currency you want the values to be summarized in. For example, I have this set to Canadian Dollars (CAD $) in my personal spreadsheet since I live in Canada. International investors can convert all values to their local currency with the click of a button!
The account filter cell (C4) allows you to filter all outputs by a specific investment account. By default this is set to show all accounts, but you can use this feature to drill down on the value and performance of any individual account.
The dashboard also shows pie charts for how your total portfolio value is broken down into:
- Individual holdings
- Investment categories
- Investment accounts
- Currency exposure
With this view, you can get a quick snapshot of the total value of your portfolio, and a view of how your investments are allocated.
Scrolling down on the “Dashboard” tab, you’ll be able to track the performance of your portfolio over time.
Enter a ‘start date’ and ‘end date’, and you’ll be presented with an overview of how your portfolio grew from start to finish, along with the money-weighted return of your portfolio over that time period.
And just below that, you’ll find a breakdown of your overall portfolio performance by investment category.
Monthly Performance Tracking [back to top]
The “Monthly Performance” tab gives you a month-by-month view of how your portfolio is growing, your investment returns, and also allows you to benchmark your performance against the S&P 500 index and a custom benchmark of your choice.
Follow the instructions at the top left, which will explain how to use this tab. It will require you to “authorize” a script to run in the spreadsheet. The tab uses a few lines of code to cycle through each month in your trading history and record your performance values.
Your Portfolio Holdings [back to top]
Digging one level deeper, the “Your Portfolio Holdings” tab shows details about your individual investments.
For each of your holdings, there is info on:
- The number of shares owned
- Your adjusted cost base
- Current market value
- Your unrealized gains
- Your dividends received
Dividend Tracking [back to top]
This tool will also give you a visual overview of your dividend progress over time.
You’ll find a trending report for the last 5 calendar years, and the last 24 months.
Realized Gains [back to top]
The Realized Gains tab shows your profit or loss on shares that have been sold.
Gains are calculated based on the price at which you sold, minus the adjusted cost base of your investment.
The spreadsheet can generate a realized gains report over any date range, and provides various summaries — by account, by category, by currency, and also shows the realized gain for each of your individual holdings.
Re-Balancing Your Portfolio [back to top]
To help you stick to your targeted asset allocation as time goes on, you can use the “Re-Balancing” tab.
After plugging in your target asset allocation values, the tool will provide you with a re-balancing plan to get your portfolio back in line with your ideal weightings.
In the example below, the spreadsheet shows how a contribution of $20,000 would be divided between your investment categories to re-balance the portfolio weights back to your target asset allocation. The results are shown in the cells highlighted in blue.
By default, the “allow for sells” assumption will be set to “No”. This means that the re-balancing calculations will not plan for you to sell any of your current investments in order to re-balance your portfolio.
I’ve set it this way since many people are reluctant to sell parts of their portfolio (preferring to re-balance by adding in new money), and also because selling your investments can trigger capital gains taxes.
If you’d like the re-balancing calculations to allow for sells, just switch the assumption to be “Yes”.
Troubleshooting Tips for the Investment Portfolio Tracker [back to top]
This spreadsheet pulls in the market value of your investments through Google Finance. Occasionally Google Finance will return errors for certain investment symbols or certain dates.
The “Dashboard” tab includes a warning at the top of the tab when it is detected that Google Finance is returning errors.
If you are running into this issue, please try the following:
- Refresh your browser
- Read through the FAQ section below — especially item #5 about using a new copy of the spreadsheet
- If none of that works, you can enter the prices of your investments manually on the “Your Portfolio Holdings” tab, in columns H, AF, and AL (look for prices that are appearing as zero, and manually input the correct price in the next column)
Frequently Asked Questions (FAQs) [back to top]
To keep track of regular dividends that are received as cash:
- Enter a “dividend” transaction on the Trade Log tab, where you input the date, ticker, total $ amount, and investment account where the dividend was received
- It’s not necessary to enter the quantity of shares or amount per share (however, no issues if you do want to enter this extra info) — just entering a total amount in column G is sufficient
To input dividends that are reinvested into new shares (a “DRIP” strategy):
- Enter your dividend as two separate transactions. One transaction is the regular “dividend” transaction, and the second transaction is a “buy” transaction to record the purchase of additional shares
- For example, assume you get a dividend of $20 which was reinvested into 4 new shares of XYZ Corp. Enter two transactions: a “dividend” transaction where you receive a total amount of $20, and then a separate “buy” transaction where you buy 4 new shares of XYZ for $5 per share
- The spreadsheet will now record that this dividend has been reinvested to purchase additional shares of XYZ
Please go to the google finance website (https://www.google.com/
Then, use that full ticker symbol in the investment portfolio tracker, on the Setup tab. Make sure that you delete any spaces in the ticker name.
For example, for the Bank of Nova Scotia company on the Toronto Stock Exchange, enter “TSE:BNS” instead of just “BNS”.
For any stocks or mutual funds which don’t appear on Google Finance, you can do the following in order to have them included in your total portfolio:
- Add a new holding on the Setup tab — any ticker is fine as long as it isn’t an actual ticker on google finance (for example, “$TDB900” for the TD e-series 900 Canadian Index fund)
- Then, use the manual price entry columns on the Your Portfolio Holdings tab (columns H, AF, AL) — since Google Finance won’t return a price per share for these tickers automatically, the spreadsheet will use these manual entry prices instead
Now, the Dashboard tab will show these stocks or mutual funds in your total portfolio value.
Similar to the answer above, try entering the full google finance ticker for the company, including the stock exchange symbol.
For example, for the Bank of Nova Scotia company on the Toronto Stock Exchange, enter “TSE:BNS” instead of just “BNS”. There is another company with the ticker BNS that is traded on the New York stock exchange, so google finance gets confused if you don’t enter the full ticker.
If the market prices still look wonky, go back to the Dashboard and check the date cell (C2). All of the market prices will be pulled as of that date, so much sure that it’s set to the date you want to see!
Absolutely! This investment portfolio tracker can be used by investors in any country. The spreadsheet even allows you to track investments in multiple currencies (up to five), all in one place.
For the calculations and outputs in the spreadsheet, all amounts are converted into a single common currency based on the currency that you select at the top of the Dashboard tab (cell C3).
So, if you now select GBP (British Pounds), all values will now convert to GBP. Likewise for CAD, USD, JPY, etc…
If something just seems off in the spreadsheet — values of “n/a”, “#REF”, or “Error” for example, it’s possible that some formulas were broken accidentally as you inputted your data.
The best way to proceed is to open a fresh copy of the spreadsheet.
Once you’ve done that, copy the values from the “Setup” and “Trade Log” tabs of your old version, and paste them into the fresh spreadsheet.
9 times out of 10 this does the trick!
This spreadsheet calculates the total return of your portfolio over any given time period — including realized gains, unrealized gains, and dividends.
Go to the Dashboard tab, and scroll down to the Performance section. Once you enter a start date and end date, the spreadsheet will calculate the “money-weighted” return of your portfolio.
The money-weighted return is equivalent to the “internal rate of return” (or IRR) of your portfolio. This return calculation captures portfolio contributions, withdrawals, realized / unrealized gains, and dividends received.
If the time period you’ve entered is greater than 1 year, the result will be shown on an annualized basis. As an illustrative example, the tool would show a return of 7% per year instead of a total three-year return of 22.5%.
If the time period you’ve entered is less than 1 year in length, the result will not be annualized. For example, if your portfolio has earned a return of 5% over 3 months, the spreadsheet will show a return of 5%, rather than an annualized value of 21.6% (which would be misleading in my opinion).
By scrolling further down in the Performance section, you’ll also find the money-weighted returns shown for each of your custom investment categories. This allows you to compare the relative performance of different groups of investments (e.g., U.S. stocks, Canadian stocks, bonds, etc.).
Please note that the spreadsheet does not calculate the money-weighted return of individual stocks at this time.
However, you can view the unrealized gains and dividends received per stock over the life of the investment on the “Your Portfolio Holdings” tab.
You can also view your realized gains per stock on the “Realized Gains” tab.
This tool wasn’t built to track cash. However, if you do want to keep tabs on your cash balance as well, you can try this workaround:
- On the “Setup” tab, add a new stock ticker called $CASH (or something else that doesn’t return a market value on google finance)
- Whenever you get extra cash in your account — dividends, contribution, etc., enter in a buy transaction on the Trade Log where you purchase X shares of cash at $1 per share (X being the total amount of added cash)
- Then, on the Your Portfolio Holdings tab, in the manual price entry columns (H, AF, AL), input a value of $1 per share for your $CASH investment
- Now, you’ll see your new ticker $CASH, and the corresponding total $ amount reflected
- If you use up that cash, remember that you’ll have to enter a corresponding sell transaction
I’ve written a script (a.k.a., a few lines of code) that runs when you click the “Go!” button.
This allows the spreadsheet to cycle through each month in your trading history and record the performance of your portfolio — starting value, contributions, withdrawals, investment returns, and the ending value.
Specifically, the script will copy the numbers in row 76 of the Dashboard tab, and then paste those values into columns N to T of the Monthly Performance tab.
Please note that these numbers as “pasted as values”. Therefore, they will not update if you add new transactions to the trade log that impact a prior month.
If you do update your trade log to reflect new transactions in prior months, you just need to delete the numbers found in columns N to T (delete only the data, not the columns themselves), and then re-run the script.
Yes! The spreadsheet can handle stock splits and reverse splits swimmingly.
To enter a split transaction, you need to input the following information in a row on the trade log tab:
- Date when the split occurred — in column B
- Transaction type of “Split” — in column C
- Stock ticker — in column D
- Split Ratio (new shares per old share) — in column J
You can leave columns E through I empty.
To illustrate with an example, let’s assume that you purchased 10 shares of Apple (AAPL ticker) on August 1st, 2020, for $415 per share.
Apple subsequently split its shares on a 4-for-1 basis on August 28th, 2020.
This means that you hold 40 shares after the split, given that you purchased 10 shares initially, and then the split occurred, giving you 4 new shares per 1 old share.
To reflect this on the trade log, you would need to enter two rows of data — one BUY transaction, and one SPLIT transaction:
Now, when you flip to the Your Portfolio Holdings tab, you’ll see the correct values:
Notice how you now own 40 shares of AAPL, and have a cost base of $103.75 per share — this is because your initial cost base of $415 per share was divided by 4 after the split occurred.
When you enter a split, you can leave column I (Investment Account) blank.
You only need to enter a split once even if you hold the same ticker in multiple accounts — it will automatically be applied across all accounts when you enter it once on the trade log tab.
In some cases, companies will perform a “reverse” split, where your number of shares will decrease after the split. This should be handled in the same way, where you enter the appropriate split ratio in column J.
For example, if a company does a reverse split on a 1-for-10 basis (1 new share per 10 old shares), you can enter either “0.1” or “=1/10” in column J. The two methods are equivalent.
Unfortunately not.
For those keeping score at home, you’ll know that I have a preference for excel over Google Sheets. However, I haven’t been able to find a reliable way of getting current and historical stock/ETF price data to import automatically into excel.
While Office 365 has a new “Stocks” feature that lets you import real-time stock prices into excel, this unfortunately doesn’t work for historical prices (yesterday, last month, last year’s price, etc).
This spreadsheet uses historical pricing info to calculate portfolio performance between any two dates, so historical prices are a critical input.
Yes they are! This spreadsheet tracks crypto currencies in the same way that it tracks more traditional stocks and ETFs.
On the Setup tab, input the ticker as the label for the crypto currency (e.g., BTC), followed by the label for the currency that it is priced in (e.g., USD). So, if you buy bitcoin with US dollars, the code is “BTCUSD”.
In the screenshot below, I’ve shown the tickers for Bitcoin and Ethereum, first priced in US dollars, then priced in Canadian dollars.
Next, use the trade log to input your transactions as usual.
Now, the spreadsheet will track your crypto currency investments and will include them in your total portfolio value, asset allocation, and all performance calculations.
Yes — the spreadsheet does support fractional / partial shares. Here’s an example portfolio showing an investor buying 0.4 shares of Apple and 0.8 shares of JP Morgan.
If you flip between the Trade Log, Dashboard, and Your Portfolio Holdings tabs, you can see the calculations properly reflected.
In your spreadsheet, you’ll just need to increase the decimal points shown in the columns where the spreadsheet shows the number of shares you hold (I did this in the example I created). As it stands, the spreadsheet shows 0 decimals by default, but you can easily show more decimals by using the toolbar at the top of the sheet.
By default, the spreadsheet is set up to accept dates in MM-DD-YYYY format. For example, February 15, 2021 would be inputted as 02-15-2021.
To change the date formatting to your liking, you can update the format manually in the Trade Log by selecting the cells in the Date column, then Format > Number > More Formats > Custom Number, and then type in your preferred format — e.g., DD-MM-YYYY or YYYY-MM-DD.
You can use this process for any and all other dates cells as well — e.g., at the top of the Dashboard tab.
This tool was built with fairly ‘vanilla’ investors in mind (buying individual stocks / ETFs), so unfortunately it does not handle options or derivatives well.
However, you can try this workaround:
– Add a new ticker symbol on the Setup tab (for example, AAPL-CALL if you are buying calls on AAPL)
– When you buy a call, add a buy transaction on the AAPL-CALL ticker, reflecting the premium paid
– When the call is exercised or expires, enter a sell transaction on AAPL-CALL at the price at which you sell ($0 if the option expires)
The price of this ticker won’t be tracked in real time since google finance does not recognize the ticker, but once you close your position out, the returns will be reflected in the spreadsheet’s performance calculations.
ETFs can give “distributions” in a few different forms. Depending on the type of distribution, there will be different impacts on the adjusted cost base of your investment / different tax consequences.
Here are some good guides for understanding fund distributions:
This spreadsheet allows you to enter various transaction types on the Trade Log — including dividends, return of capital, reinvested capital gains distributions — and will adjust your adjusted cost base accordingly.
Final Thoughts [back to top]
I hope that you’ll get good use out of this investment portfolio tracker. If you have any questions or feedback, please leave a comment below or send me an email at themeasureofaplan@gmail.com.
I’ve gotten amazing feedback on the spreadsheet from investors from all corners of the globe, and I’ve been improving the spreadsheet with new features and outputs on a regular basis (see the change log tab for more details).
When I upload a new version of the spreadsheet, you’ll see a little red box notification at the top of your “Instructions” and “Dashboard” tabs. You can also simply check this page every month or two to see if anything is new.
And that’s a wrap — cheers to good health and growing wealth!
P.S., if you’d like to show your appreciation for this spreadsheet, you can buy me a coffee or beer by clicking the little blue button below 🙂
—
Header image credit: The Sacramento Bee
—
Hi,
Possible to record scrip dividend
Receiving extra shares at zero cost.
Thanks
Hi,
Quick question on how realized gains are calculated. The spreadsheet seems to do the calculation based on stock purchased cost if owned in multiple accounts but sold in one account. For example if there is an RRSP and a TFSA, both with BCE shares. If BCE shares sold within the TFSA only, the Cost Base is calculated based on both the RRSP and TFSA values. Am I interpreting this correctly?
Thanks.
Yes Mark I believe you are calculating this correctly. The cost base is not calculated separately account by account. Eg. you purchase 1 stock in tfsa, 1 stock in rrsp, 1 stock in unregistered account, your cost average is a combination of all 3.
If you select the account in the dashboard you will get the correct cost base.
Hi,
I would like to use the template with Eurpean formatting (date, currency, etc), but when I change something, the program no longer responds.
Is there a way to correct this?
Thank You in advance
Hi,
Please ignore my previous mail. The problem is solved since I am using the new version on Google.
I have positions with BMPS from beginning of 2022. On September they made a reverse split of 1:100.
After the split, I purchase more shares. Is it possible to identify the older shares before the split from the new ones after the split?
If I leave the original values, the calculations are completely wrong.
Thank You in advance for your support
Kind Regards
Hi Francesco,
On the date of the reverse split, please add a Split transaction the trade log, with a split ratio of 0.01 (reflecting 1 new share for each 100 old shares).
This will adjust the old shares so that it is comparable to the new buys that you are making.
See the FAQ section of this page for more details.
Hi can you post your methotology and ways that you used to calculate your time weighted return and money weighted return ? I can’t seem to get the same answers.
Hi Zheng,
For money-weighted and time-weighted returns, these are the methodologies:
https://www.investopedia.com/terms/m/money-weighted-return.asp
https://www.investopedia.com/terms/t/time-weightedror.asp
The money-weighted return is calculated in the spreadsheet on the “Calculations” tab (see rows 2202 to 2220).
The time-weighted return is calculated on the “Monthly Performance” tab, using the monthly return calculated in each period.
[…] once you’re up and running, you can use this investment portfolio tracking spreadsheet for a neat and tidy way to keep tabs on your […]
How does one input a short sale of an equit? I only see buy and sell options. There is no “sell short”.
Hi,
I apologize in advance if this was already asked. How would I input a corporate action like the one of Brookfield where I received shares of BN.TO and BAM.TO for shares of BAM-A.TO? I have input them as buy/sell transactions for the time being.
Thank You
Canadian stocks like TD.TO, google finance cannot retrive price, anyone asked this question before?
For GoogleFinance formulas, Canadian stocks have to be in this format:
TSE:TD
Exactly. Thank you Mark!
Has anyone figured out the new issue with “.UN” stocks?
For example, TSE:KEG.un worked up until the last week but now I can’t figure out how to get it to pull data.
Another holding I have (previously TSE:HOT.un) works when changed to TSE:HOT.U but that doesn’t work work with my other “.un” stocks.
Hi, Is there any way for the spreadsheet to handle ‘Transfer in Kind’ from one account to another? For example moving 100 shares of RY from a RIF account to TFSA.
Thanks
Hi Mark, there isn’t a way to do this directly in the spreadsheet.
You could input a sell transaction from the RIF and a buy transaction in the TFSA on the same day.
Alternatively, you could simply re-label the initial buy transaction to reflect the account where that investment resides now.
For example, if I bought 100 shares of RY on June-30-2022 in my RIF, then transferred to a TFSA later on, you could change the initial transaction to be within the TFSA instead.
Footnote: depending on the accounts used, transferring securities can trigger ‘deemed dispositions’ for tax purposes.
So, for example, when moving a stock w/ share price gains from non-reg’d to reg’d accnt, you don’t get to “import” the gains into the tax shelter; CRA will consider you to have sold at market price, realized taxable investment gains and deposited the security into reg’d account at that new cost base.
Is there a way to also add interest income ? The cash I have in my accounts generate a substantial amount of income as well.
Hi Popo,
See the FAQ section of this page, particularly item #7.
You can track cash positions in this way.
Hi, I am getting an error on the monthly performance. Says “Exception: Range not found”.
Any idea?
fixed. I made a typo on the trade log.
Thanks for confirming Priyank. Happy tracking!
THANK YOU so much for your awesome budgeting and investment tracker excel/google templates first off!
Question… Setup/trade log work flawlessly and display proper data. The issue I am having is that the Total amount(before trading fees at 0%) do not match the “Dashboard” tab’s data. For example $57,449.91 is accurately displayed in trade log, but in the dashboard this same figure is displayed as $77,133.00. Not sure exactly what is happening and how to remedy this. Thanks.
Hi Dave,
Please check the currency used at the top of the Dashboard tab. Perhaps the investment you made is in USD, but the Dashboard is calculating the values in CAD (Canadian dollars) instead?
If so, just switch the Dashboard tab to USD and it will display correctly afterwards.
What a silly mistake, that did the trick. Thanks again.
Hi, is there any recommended way of inputting positions in GICs? Would you code them as $CASH positions like in your example? Thanks!
Hi Jay — yes exactly, I would use the $CASH method for GICs (you could use a ticker of $GIC for example).
I don’t know what’s wrong with my spreadsheet, but under “Portfolio Holdings” there are figures for stocks even though I haven’t entered any trades for them yet..
Been using this spreadsheet for a couple of years and been super helpful!
One issue that is preventing me from continue using it is using funds e.g. vanguard 100% lifestrategy fund.
Is there any way to incorporate this / deal with it as I really love this sheet.
Why is it only possible to manage 5 currencies? Or is there a way to insert more?
Hey there,
I’m getting a notification that the script for “getHistoricalPerformance” could no longer be found on the “monthly performance” tab. It was working about two weeks ago.
If you don’t mind digging in on your end that would amazingly helpful.
Thank you so much for this tool.
Alan resolved this.Turns out it wasn’t anything but a fix on Google’s end.
Some ETF’s are returning N/A. Been like this for a week now.
More precisely SXR8 (XETRA exchange) and CSPX (London exchange)
Do you know why it doesnt work? It is live on Google FInance website with the correct ticker?
Issue on the Google Side:
https://issuetracker.google.com/issues/282426429
Did you try entering the entire stock name? For example when i’m in Canada I need to use TSE:BNS
This is truly a masterpiece. I’m trying to figure out a workaround for VTWAX, since it has two categories (US & Foreign Equity). I can pull this from Vanguard’s website, but how can this be accurately captured specifically in the Dashboard? It doesn’t really make sense for it to be it’s own category.
Secondly, the Monthly Performance tab, columns N-F don’t populate. I’ve entered all my historical data in the trade log, but it isn’t captured here. Please help!
Hi,
Thanks for the spreadsheet. It seems like the dividends are not taken into account in the returns calculations. Is that correct?
Thanks.
Hello!
The Monthly Performance script repeatedly exceeds the maximum execution time. I have run it about 50 times now and it has only populated 3 months of data (out of 147). When I run the script through Apps Script it shows “Performance Calc Error is still active” every 4 seconds. Is it supposed to take this much time, or is there something I have input incorrectly? Thanks!
I am having the same issue and sometimes get different results with the same data. Hopefully the developer will notice our questions
Love this spreadsheet, but having trouble getting the spreadsheet to recognize 2 of my holdings, Fortis ticker FTS, and Granite Real Estate Investment Trust ticker GRT.UN. Any ideas to get the spreadsheet to recognize these holdings. Thanks in advance.
First of all – great tool, just starting using it.
Got a question, what would be the best way of adding de-listed stocks and their dividends. I.e., had some BPY.UN REIT in CAD, it was then delisted from TSX and obtained cash + BAM + BPYP.
There was a lot of confusion steps in between. Since I cannot add delisted stocks how should I do it in order to properly track it all? Thanks
Hey just started using your spreadsheet and still figuring it out.
I have Fortis on the TSE. Ticker FTS.
I have put it in the stop page as TSE:FTS, checked the google finance page and that would be the correct entry but nothing shows up.
There is another one that doesn’t show up but Google isn’t recognizing stocks on the TSE with “.UN” in my case CAR.UN is a REIT. But that is a google problem not a glitch in your googlesheet.
Thanks for the help Steve Hogg
Sorry I didn’t proof read, setup not stop on the third line.
There seems to be many issues this month with Google Finance tickers not working properly (https://issuetracker.google.com/issues/284822200).
I’m wondering if there’s a way to import data from Yahoo Finance if the GOOGLEFINANCE function stops working…
Hi Steve, I have the same problem with both FTS and anything that has the .UN in my reits. Not sure what the problem is here either, but we’re in the same boat!
Hey folsk, I was able to get a not very-so-stable formula running and get the from Yahoo Finance data.
I added this small script to read the CSV from their API
__ const GETCSV = url => Utilities.parseCsv(UrlFetchApp.fetch(url).getContentText()); __
And then added the values into the “Manual Input” cells in purple in this file
https://docs.google.com/spreadsheets/d/1GxI5aj2eKkPbMe4xxgVwNgvbtNxEfB7T2Tuxtzi3DLg/edit#gid=1111357939
Keep in mind it’s not perfect. I’m having some issues with the dates. It seems the calculation doesn’t return always a value (e.g holidays and weekend) but I couldn’t figure out why.
Maybe someone smarter than me can connect the dots. 🙂
Hope it helps
Hello
I would like to ask a question. Recently, there have been wrong data on the market value of different holdings every day. What is the problem?
Thank you for your reply
Hello,
Thank you so much for providing this form for everyone.
How do I include the CD interest rate in the form?
I had JNJ stocks and I change to KNVUE stock. How can I manage this?
Thanks
Looking for some clarification about the rate of return calculations as they relate to DRIPs.
Considering we add additional shares as ‘buy’ in the Trade Log, it’s essentially considering those amounts as returns (the total dividend) as well as contributions (the fee-less shares acquired with the dividends).
For all I know this is standard practice in the calculation of RoR, but I find it confusing if I want to see what my overall monetary investment (contribution) to my portfolio has been.
Hi Steve
I have exhausted all 250 rows in setup , how do I add more.
[…] INVESTMENT PORTFOLIO TRACKER DI THE MEASURE OF A PLAN […]
Thank you for sharing this. My account has some foreign taxes and annual fees. How do I handle that in the portfolio tracker? Typically my advisor sells shares to cover the cost
Dear Steve,
I have been using the Google sheet from quite sometime .I have exhausted all 250 columns in setup for tickets and would like to add more .How do I do it
Amit
Hello,
Would it be possible to get a dividend bar added to the bar chart on the Change in Portfolio Value table on the Dashboard?
The number is in the table, just no actual bar.
Hi,
Firs of all, I would like to thank you for this great spreadsheet. Thanks for sharing with everyone at free of cost.
I own LAC before it gets split in to LAC and LAC. Now my portfolio is imbalance due to this. How can I handle this in the spreadsheet. Kindly guid me. Thank you.
The spreadsheet is great, but I wish it could handle more types of investments. Good start to include ETF’s and Crypto, but what about precious metals like gold or silver. The market values are freely available on Google Finance, why not make them available as an investment class. People want to be diversified across multiple asset classes, not just stocks. Therefore the sheet has limited value for me presently.
Thanks so much for sharing this google sheet, it is the best and even better than the paid software.
I have 1 request, would you enhance it to support writing short call/put? I try to record those trade as sell to open then later a buy to close, but that would make the option’s holding qty being wrong. Thanks.
really hope can support 20 accounts as I want to these: TD RESP, TD TFSA, TD RRSP, questrade RESP, questrade TFSA, IB cash, TD cash, …, need more than 10 accounts
Thank you for creating and maintaining this spreadsheet, this is incredibly useful and extremely well done!
One feature I would love to see implemented is the expense ratio (ER) for my index funds (ETF and/or mutual funds). There is a fee in the trade log but it looks to be a one time for the trade, not a percentage ER of the ticker owned by an index fund.
Another piece of information that would be nice is the tax burden (unrealized gains) on tax-deferred accounts and brokerage accounts. This tax liability could then be copied into a net worth statement.
Thanks for the hard work!
Hello,
One suggestion that would help with my use case is being able to specify the owner of the transaction (myself, spouse, or joint). Retirement accounts are “individual”, but when building a portfolio, our household groups our holding when it comes to asset allocation and location.
It would be convenient though to know how the portfolio is spread amongst the individual owners.