Timestamps and code examples (see Pastebin) are in the description. I also didn’t get a chance to add the waterfall graph of portfolio return (second graph in the thumbnail), but I can share how to make it here: 1. Select both the Symbols and Portfolio Return (%) by holding Ctrl (Command on Mac) and selecting C6:C26 and U6:U26. 2. With both of those selected, in the top menu click Insert then Chart. Once the chart appears, in the Chart Editor under the Setup tab click the Chart type drop down menu. 3. Scroll to the bottom and select the Waterfall chart option (should be in the ‘Other’ section). There you go! Waterfall charts are the best visual representation for the contribution of individual investments on total portfolio performance.
Got a couple questions if anyone could help. 1. How would I convert multiple currencies into using sheets and importing stock data from google finance. For example having investments in USD, GBX and GBP. I need a formula that can distinguish what’s not GBP and convert using the current currency rate in order to achieve overall figures in one currency (GBP) 2. Whilst Creating a portfolio tracker using Google Sheets. What’s the best way to to either add previously purchased stocks or adding a newly purchased position on top of an older position of the same stock. Example, if I’d purchased 1 share of NKE at $100 and then months later bought another 1 share of NKE at $110 how would I show this when creating a new portfolio in addition to adding more shares of NKE in the future.
Great sheet - thanks for sharing! How do you insert/create a chart from your sheet that shows how much you have in each SECTOR to make sure the portfolio is not heavily weighted in one SECTOR?
Great flex!!! When you point out none of the values are red lol Great Video If I ever organized and trim my portfolio definitely got to use this. I use too many brokerages .
Thank you so much for this! I am a complete newbie to spreadsheets and am really impressed with your knowledge and application of formulas. I built my spreadsheet by following your tutorial and it looks great! I am hoping also hoping you can help me integrate sold positions to track gains and losses.
This was a great video, Mike. I have a mountain of super technical spreadsheets and it was nice to take a step back to see it from the perspective of an ordinary investor who probably isn't immersed in finance all day everyday!
Exactly, this is what I was thinking. If all you do is buy and hold, this system works fine, but if you want to sell and periodically take profits from your stocks, you need a way of tracking your realized gains (or losses). There is a great youtube channel called Think Stocks that goes into a Google sheet portfolio in which it is possible to track all your unrealized and realized gains. The only problem is that you need to write the code yourself, and it gets very tricky!
This video is a month old and you have GME as a "Special Situation"... I've got my eye on you. How did it feel to sell those 2,000 shares at $330.00 a piece for a total of $660,000?
If you're having trouble like me, this might help: You have to have your Google Sheets set to English. Simply google how to do this and then follow instructions. Then it works perfectly.
Great Video! Could you have in the next one how to keep track of the growth history, day by day? So you can see how each assest is performing through time. Thanks Micheal!
NONE are working for me. I have no clue why. Not the simplest works either. Like trying to display the current price is impossible. Did you figure it out?
can you please do a transaction google sheet which updates the portfolio? this is for the day to day buy/sell of the stocks in the portfolio. let me know plz Thx
Got a couple questions if anyone could help. 1. How would I convert multiple currencies into using sheets and importing stock data from google finance. For example having investments in USD, GBX and GBP. I need a formula that can distinguish what’s not GBP and convert using the current currency rate in order to achieve overall figures in one currency (GBP) 2. Whilst Creating a portfolio tracker using Google Sheets. What’s the best way to to either add previously purchased stocks or adding a newly purchased position on top of an older position of the same stock. Example, if I’d purchased 1 share of NKE at $100 and then months later bought another 1 share of NKE at $110 how would I show this when creating a new portfolio in addition to adding more shares of NKE in the future.
How can I create a chart of any equity priced in any equity. For example, DIA/SPY. That is, the Dow 30 priced in S & P 500, instead of in dollars. I found one website that has many things priced in gold, which is a good source to see relative values. But how can I create a chart of AAPL priced in SPY? Or IBM priced in DIA?
You could simply divide the price of all your assets by the price of the asset you want them denominated in. I.e. if you wanted everything priced as the number of SPY shares, just divide all USD prices by the current USD price for SPY.
These Videos are great... but one issue I'm having - probably a Noob Issue, sorry - is that when I try to Open the Sheet I made from Google Sheets to the Desktop App Excel, alot of the Formulas get Broken and don't work anymore. Is there a trick to convert from Sheets to Excel?
Not sure how I'm screwing up a copy and past, but for your O column it just says TRUE, but doesn't list the price of the stock despite the symbol entered in C. Actually none of the formulas I've copy and pasted are showing up despite being in the wrote row and column. Any ideas what I did wrong? Thanks for the video.
Got a couple questions if anyone could help. 1. How would I convert multiple currencies into using sheets and importing stock data from google finance. For example having investments in USD, GBX and GBP. I need a formula that can distinguish what’s not GBP and convert using the current currency rate in order to achieve overall figures in one currency (GBP) 2. Whilst Creating a portfolio tracker using Google Sheets. What’s the best way to to either add previously purchased stocks or adding a newly purchased position on top of an older position of the same stock. Example, if I’d purchased 1 share of NKE at $100 and then months later bought another 1 share of NKE at $110 how would I show this when creating a new portfolio in addition to adding more shares of NKE in the future.
This is my first time using google sheets. I'm trying to copy the video exactly, but I must be doing something wrong, because when I copy and paste the formulas from the pastebin into the sheets, everything simply comes up as "true' or "false" with no number values. Anyone know what I can do to fix this? Thanks
Great video Michael! How do I add a security in the ASX exchange to differentiate from NYSE. For example BHP. It is listed on both exchanges and I want to be able to see the comparison in the spreadsheet. Thanks!
What happens when a stock does a split? That would throw off all of the calculations of "position value", "Amount Paid", "Total Return", etc. This is because the current price of the stock would mislead all of the algorithms.
Yes, a manual adjustment would likely be needed to correct for the split. One of the reasons I am not a fan of stock splits. They create no economic value and just add marginal amounts of paper work for everyone else.
Hey there, wondering how I can get some of the american stocks with no alternative ticker symbol to read in canadian dollars (as I'm from Canada). Any insight would be appreciated, great video!
Great value! I have a couple questions 1. Why did we add the Dashboard tab? Is that for a later tutorial that builds on this one? 2. Is it possible for you to show us how we can add newly purchased shares via a different tab (how do you approach this?)
Hi Brian, yes! My plan is to expand the dashboard tab with several metrics and portfolio visualizations in a future video. One way to approach #2 that would be to create a transaction tab to log your transactions and then create formulas on your tracker tab to calculate your # shares and cost basis.
@@MichaelJayValueInvesting Thanks for replying. I've been through this video several times today still extremely impressed with the level of value your provided. Thanks for doing this! One more question: I have investments in Tax advantaged and non tax advantaged accounts. Do you personally use different trackers per portfolio or do you lump all into the same one?
@@MichaelJayValueInvesting Could you make a video on creating a transaction tab to log your transactions and then create formulas on your tracker tab to calculate your # shares and cost basis?
Thank you Michael. I really appreciate the work and time you put into this. Really detailed and your explanation can't get any better. Thanks again. I look forward to seeing more of your videos in the future. Good luck in your investments. ✌
Hello Michael! Thank you so much for your vid. I just finished mine. :) I have some questions thou since I'm new to investing. What other asset class are there? How do I input crypto? I also deleted my subportfolio because I don't know what to put in there. What do growth, spec growth, special situation, etc mean? What do I put for sector and subsector of stock like LFT? I put REIT for sector and Mortgage for subsector. Not sure if those are right. Last but not the least, if I received dividents but I reinvested it, does that mean the dividents go back to $0? I hope you can help me. Thanks a lot.
For sector and subsector, I use the Finviz definitions for each stock which you can look up on Finviz. (These aren't too important though, so don't get hung up on them.) For subportfolio, that was just a way for one to organize their portfolio if they were running different strategies (or subporfolios) within the main portfolio. For reinvesting dividends, record the dividend and then a separate purchase of the stock. This will count your dividend as return but also increase the cost basis. For crypto, there isn't much google finance support beyond BTC at this time. Hope this helped!
You select the sector instead of the ticker or name as your input. You're still selecting the allocation for output. As long as you have the relevant columns selected, just play around with it until it looks the way you need it to.
You could have a separate tab for currency conversion rates and then multiply your USD prices by the appropriate rate to get to CAD. You may need to use an absolute reference ($ dollar signs).
