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:
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
2) Tips & Tricks
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:
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.
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.
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:
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.
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.
Digging one level deeper, the “Your Portfolio Holdings” tab shows details about your individual investments.
For each of your holdings, there is info on:
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.
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.
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”.
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:
To keep track of regular dividends that are received as cash:
To input dividends that are reinvested into new shares (a “DRIP” strategy):
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:
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:
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 are “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:
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.
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
—
View Comments
Holy moly this is incredible! I love having all my investments in one place and being able to see the current value without manually plugging in the stock prices myself.
Thank you so much!!
Hey Sam,
That's awesome to hear! Thanks for sharing.
I will definitely check out your spreadsheet, it looks like it might be very helpful and build on my own spreadsheet.
I have been using this method to get my stock quotes from Google Sheets into Excel. The only downside is the manual step of "refreshing all data" to get the latest stock quotes.
https://www.vertex42.com/ExcelTemplates/excel-stock-quotes.html
Hi Dan, thanks for the heads up on that query method.
Unfortunately this spreadsheet is a little bit more complicated than that; information would need to be passed both ways (from the excel sheet to the google sheet and vice versa), as opposed to only from the Google sheet to excel as outlined in your link.
The reason is that this spreadsheet calculates portfolio returns between two selected dates (both dates can be changed). As such, the excel file would need to pass date info to the google sheets file, and then the resulting stock prices would need to be passed back to excel.
It's a shame that excel discontinued their direct stock price lookup feature...
I am having a problem with one stock symbol TCAP google finance enters a price that is not correct much higher than the price quoted by other services. tried refresh, exit and reload, reboot, etc. can this be resolved? I sure hope so because this is exactly what I have been looking for... Thanks so much for your effort...
Hi James,
Is the company you are looking for named "Triangle Capital Corp"? That company appears to be called "Barings BDC" now. Google Finance lists the ticker for that stock as BBDC as opposed to TCAP. Perhaps the ticker changed...
Can you try BBDC and see if it works?
In general, the best way to make sure the ticker is correct is by searching for the company at google.com/finance and plugging in the ticker that Google uses. Sometimes companies change tickers, sometimes you need to add the full ticker (NYSE:BBDC) because there is a company on the Toronto or London stock exchange with the same ticker.
Hope this helps.
Problems solved quickly. Faster than any support resolution that I have experienced...
Cheers, glad to hear it Jim!
Thank you. That was the problem. Two other questions if you have time... 1. The totals line on portfolio holdings sheet (line 56) does not show any totals.. 2. The dashboard page doesn't work. A lot of "#N/A". Thanks so much. Jim
Hey Jim,
Sorry to hear that. Have you read through the Troubleshooting Section of this post? There are a few things you can try there. Is Google Finance populating market values for all of the stocks that you've entered? Perhaps one of the ticker symbols you entered is incorrect.
You can send me a screenshot by email, or better yet share your spreadsheet with me so that I can take a look at what is going on.
Cheers.
Thanks so much for this great spreadsheet! I love it.
Just one questions though: In the trade log I have entered the dividends received. They just don't sync into my portfolio holdings page as dividends received. Do I need to manually input the values on the portfolio holdings page? Also, are dividend received not reflected on the market value of the Dash Board? If no, where should I best look for my current net worth of my investment?
Once again, thank you so much for this lovely spreadsheet and your time for answering our questions.
Jerry
Hi Jerry,
Glad to hear that you're getting good use out of this spreadsheet.
Here is how the tool treats dividends / how dividends are factored into returns:
Your dividends received are also shown on the "Your Portfolio Holdings" tab (in column Y). On this tab, the unrealized gains for each of your holdings are calculated based on the market value of your investments as of the date value you select on the Dashboard, minus the cost base of you investment.
For the calculation of your investment returns over time, dividends are treated as 'contributions' to the portfolio to the extent that the cash your received was used to buy new investments.
For example, if you receive $100 in dividends, and use that to buy a few more shares of a stock, this money will be treated as a contribution to your portfolio, and will also be reflected in the value of portfolio at the end of the time period. If you do not re-invest this money, it will not be included in your portfolio.
To log dividends correctly in the "Trade Log" tab:
For regular dividends (without DRIP), you should just enter them as a dividend transaction.
For dividends that are re-invested, enter them as two separate transactions. One transaction is the regular dividend transaction, and the second transaction is the purchase of additional shares.
e.g., let's say you get a dividend of $20 which was reinvested into 4 new shares (implied current price per share of $5). You'd enter a dividend for $20, and a separate 'buy' transaction of 4 new shares being bought for $5 each.
This way, your DRIP investment will be tracked properly since your share count will match what you actually have.
I hope this helps!
That's very clear explanation, thank you very much. I was thinking 'hmm, where are my dividends gone?', without thinking that it could be treated as a re-investment.
Big help indeed!
I'd appreciate if you could explain a little bit more because I am not convinced that dividends are correctly treated in your tool. I am not a financial professional so that might be because I missed something...
To me, dividends contribute to the performance of the portfolio. For example, if one share of a stock is worth $100 on Jan 1st and $110 on Dec. 31st and it distributed $10 of dividends, then the returns are +$20 (or +20%). In your tool, I don't see how dividends are included in the calculation of returns. I don't think dividends should be treated as contributions because even though they are reinvested, this is not new money transferred in the portfolio. Currently, your tool treats my dividends as if I transferred new money (contributions) in my investments, thus diminishing my returns.
Another example, if I buy 5 shares at $20 each on Jan 1st (portfolio value = $100) and it still $20 each on Dec 31st, but I received $20 dividends on June 30st that I reinvested in one share. Your tool will give a return of 0 while it should actually be +$20. My portfolio cost me $100 from my pocket and it is now worth $120.
Your tool is very helpful and I thank you for sharing it but I can't use it if I am not fully convinced with the returns it provides. I hope you'll will have the time to explain me how it works!
Hey Gloubo,
I still re-iterate what I mentioned to Jerry above about how to log your dividends / reinvestment of dividends. However, you make a good point about the returns.
The return calc should factor these in.
I'm a bit busy currently, but I'll adjust the tool and update you when it is tweaked!
Thanks for pointing that out.
EDIT: As of Dec. 8, 2018, this update has been made and released in version 3.
I want to second that post and that dividends contribute to the performance of the portfolio. It would be great if a future update is created to accommodate for this.
I also second that the spreadsheet is great and very useful. A big THANK YOU for creating!
Thanks FS.
I've just released an updated version 3 of the spreadsheet that factors dividends into the return calculation.
Cheers!
Thank you, it works and I love how easy it is to "upgrade" to the new version - only a few copy & paste actions.
Cheers, glad to hear that.
For those who aren't as spreadsheet savvy as FS, if you are on an older version and want to 'upgrade', just copy and paste your data from the "Setup" and "Trade Log" tabs from the old version into the new version. Voila!
Hey Gloubo,
Version 3 of the spreadsheet has now been published. The money-weighted return calculation now factors in dividends.
Thanks again for pointing that out!
This is great! Thanks for the quick update.
Quick question. If I have a previous version the only way to update is to change the file I guess? I'm asking because I made few personal updates as well after I copied the file and It might be hard remembering what I tweaked.
On an unrelated note. It'd be great if you can keep a change log like (https://keepachangelog.com/) to keep track of all the different changes and version ;)
Again keep up the great work!
Thanks Alex.
To move over to a new version you just need to copy & paste the info from your "Setup" tab and "Trade Log" tab into the new version.
Unfortunately you'll need to replicate your custom tweaks (formulas, charts, etc.) if you want to carry those over.
Re: the change log, great idea. I will do that.
Awesome work! Just a question -- and I'm not sure if a bug or stupidity on my own part or what.
I have holdings of the same symbol in two of my accounts (different amount of shares). But in the "Your Portfolio Holdings" sheet, it shows up twice as expected but both entries have the combined number of shares. To word it more precisely, if the combined amount of shares is (80 in account A + 20 in account B=) 100, both entries in that sheet have 100 shares. And I'm almost sure this is thus recording double how much I hold of this category and such.
Is there a way to resolve this?
Hi Luis,
On the "Setup" tab, have you entered in that same symbol twice in the "Portfolio Holdings" table? If so, please delete one of those entries. The intention is for you to enter unique symbols in that table, otherwise it will double count as you mention.
The "Your Portfolio Holdings" tab will then show the combined 100 shares in one row, but only once. The tool won't be able to show the 80 shares on one row, and the 20 shares on another row if it is the same symbol you own.
Let me know if that helps!
You’re absolutely right. That was a silly mistake.
Hi, I haven't started using your spreadsheet, but like to ask if there's a way to add in more currencies?
cheers
Glenn
Hi Glenn,
You can use whichever currencies you want (just need to change the symbols on the Setup tab), as long as you respect the maximum of 5 currency symbols in total. It isn't currently possible to enter in more than 5 total currencies.
Thanks so much for sharing this.
I have entered my trades - which aren't that many. However, an issue arises when I enter the following trade:
LON:TSCO - 500 @ 2.23 GBP.
The set-up tab shows the correct total - $1,115. Yet, when I look at the Dashboard, the value of this stock is then inflated 100x to $105,00. This would be great if that were true but sadly that's not the case.
It is probably to a set-up option - where one is in cents/pence, the other is in dollars/pounds.
Any idea how I fix this?
Many thanks
Hey Damian,
I scrubbed through the file this morning. I think I've made the fix. Can you please try opening up a fresh version of the spreadsheet, and then re-pasting your old Trade Log into the new spreadsheet?
I've tweaked the formulas so that if a stock is in GBP, that the price from Google Finance is then divided by 100. Issue is that most stocks report the price in dollars, while British stocks are in pence.
Let me know if this works.
PS - unfortunately there's no way to easily format all the currency as the pound symbol instead of the dollar symbol depending on what the user selects, so you'll just have to mentally note that the figures are in GBP even though a $ symbol is showing up...
Thanks for the new version of the spreadsheet - and sorry for the delay in getting back to you. At first glance, the new version divided all my investments by 10 - so I simply reverted to the previous version, and left off the TSCO stock, for now, And the previous version works a dream. I will take a closer look at the spreadsheets later this week and get back to you. But thanks again for your help.
How can i add in KLSE stock in my portfolio? Google sheet doesnt seem to recognize it despite i'm able to find it on Google Finance (Eg. PBBANK:KLSE)
Hi Jerry,
I just tried on my end as well (KLSE:PBBANK) and it returned an error. After some googling around it seems that Google Docs cannot access price data from certain stock market exchanges for some strange reason -- even though it shows properly on Google Finance. The Malaysian market KLSE seems to be one of those.
See here...
https://productforums.google.com/forum/#!topic/docs/HDnOnJc8MRE
Unfortunately there doesn't seem to be a work-around that Google has proposed. Sorry about that.
Awesome spreadsheet! This is exactly what I've been looking for months now! :)
Just a quick question.
How is the total Contribution calculated on the Dashboard sheet? I assumed is the sum of all "Buy" transactions in the Trade Log, but by calculating the sum manually on the log sheet, I get a slightly different value (1% short) from the one in the Dashboard. Not sure why.
I checked all the functions too. Am I missing something?
Thanks in advance for the help! And again. Awesome job!
Hey Alex,
Glad to hear you found what you've been looking for.
To your question -- in your trade log have you invested in assets in more than one currency? The trade log column G is the total amount of your trade in "local" currency (e.g., in CAD if a Canadian stock, and in USD if an American stock). However, when you go to the dashboard, everything has been converted to one common currency.
For the overall concept you are absolutely right -- the total contribution amount should be equal to the sum of all your buy amounts.
Let me know if you're still having an issue reconciling.