Stock Portfolio Tracker Spreadsheet - Google Sheets Template
ฝัง
- เผยแพร่เมื่อ 12 พ.ย. 2020
- Learn how to create a stock portfolio tracker template in Google Sheets. In this video we'll use various function: GOOGLEFINANCE, IF, IFS, SUMIFS, SUMPRODUCT, INDEX, SPARKLINE and more to build a spreadsheet that will track stock portfolio based on transaction entries we make for different tickers.
Final Template (to use the template got to File - Make a copy): docs.google.com/spreadsheets/...
Web Version: docs.google.com/spreadsheets/...
With weighted averages: docs.google.com/spreadsheets/...
Video explanation: • Google Sheets - Averag...
Learn how to use GOOGLEFINANCE function • Google Finance - Stock...
#Stock #Portfolio #Tracker #Spreadsheet #Template
That's awesome! I had already created a simple tracker but after seeing this video added the more advanced tips - the sparkline trendline is amazing! Thanks for sharing.
Woohoo. I've been waiting for something like this. Can't wait to try it out. Your brilliant. Thank you for your excellent videos.
Thanks for posting these vids, you are very good at explaining the formulas and give great examples and options. Very informative!
The greatest portfolio tracking sheet teaching! I tried a while then decided to search youtube for the content and I found yours...beyond my expectation! Thx, Bro!
Welcome aboard!
Can't Thank you enough! Your Portfolio Tracker is exactly what I have been trying to create in Excel, but could never achieve. It's absolutely perfect. I'm new to Google Sheets. So, I've subscribed and look forward to increasing my knowledge of this valuable resource.
You always keeps us updated with new things about spreadsheet..
Always like it.
thanks. I searched for other stock tracking sheets online. This one is the best!
thank you very much for the sparklines! this is one of the coolest things i've ever done in a spreadsheet
By far the best tutorial on google sheets for Stock market tracker.
Thanks so much. I have followed your step by step instructions and have been using this spreadsheet for 6 months and love it.
Great to hear!
This is the most underrated video. thank you.
Thanks for this! I followed it to a tee and I'm really happy with my spreadsheet!
Great to hear!
Great tutorial. Easy to follow you, especially the formula creation. Thanks for the help.
THANK YOU! THANK YOU! THANK YOU! I was beginning to think that I was a real weirdo because I wanted a portfolio tracker that took in to account fees and re-investments. Most of the trackers I've found online don't do it, the people posting them always say that they don't pay fees, like they're ABOVE it.
watching the other videos everything became clear and I solved the errors. Thank you so much. Congrats!!
Your video is awesome. The best on managing a portfolio. Fantastic grasp on all google sheet functions. I have been using a lot of PIVOT sheets for separate views. Seems there is not much of a need for PIVOTS when using UNIQUE. With this I can have all transactions in one sheet. Great learning. Thank you.
Sempre quando volto ao seu vídeo para tirar uma dúvida eu quero dar um dar um like, mas percebo que já dei. Continue com seus vídeos, são muito bons. Saudações do Brasil.
I always watch your videos and I always learn something new. Awesome as ever thanks!
Awesome, thank you!
You wow me! Thank you for this beautiful and informative presentation. Precise and Concise.
It's really good you did in this video! You rock...I wait for your next video :)
Thank you very much for this tutorial! Your presentation is understanding clearly.
Thank you so so much!!! It's super useful, love the spreadsheet!!!!:))
Thanks! Its such a great tracker! And I won't need to update the prices myself manually. I have also added another column to drag and choose which broker I bought/sold from to make it easier for myself to check as well.
you are an amazing teacher brother!
This was AMAZING! Thank you SO MUCH for sharing so much and giving so much value!
👍
Thank you man! That is the greatest guide!
Excellent video , I had tried some others but your teaching method was excellent.
Absolutly amazing! thank you for sharing it...!
Dude, you are a Ninja! Really appreciate your explanation.
just want to say thank you dude you are awesome! I was scratching my head trying to figure out how to do this and your video has helped me a tonne! subbed!
Thank You!
@@ExcelGoogleSheets Hey, cells C6:D in "Portfolio tab" should be calculating the weighted averages instead of normal averages. If the user buys (or sells) 5 shares at varying prices, the averages will be incorrect. I was using your template and noticed this error. It was a simple fix using SUMPRODUCT but still a more advanced function than AVERAGEIFS
A history tab that shows the development of your portfolio/each position day by day would be a powerful feature for this sheet. This should be possible based on the transactions tab. A further Update with that feature would be great :-)
This is a nice way to track my portfolio. I started a simple tracker, then I added more tips. All thank to you! But to make it more efficient, if dividends are added (reinvested), the tracker would be accurate.
it’s really great! thank you very much.
DUDE! thank you so much. you're teaching me so much and your sharing of your knowledge is greatly appreciated. Wishing you well this 2021.
Thanks, you too!
Can you suggest how I could add a leveraged trading option to this amazing sheet you’ve created? Also a live equity feature?
Thanks! this is the coolest tutorial ever
Man, this is so cool. Thank you thank you thank you thank you thank you thank you thank you thank you!!!!
Amazing... I thank you for the information. It beats going to purchase a ledger
Incredible, thank you!
Love your videos. Keep it up.
Thanks a million for this video!
great vid, thanks so much!
Your brain is awesome bro. I was trying to do this for last 3months😃😃
Glad I could help
@@ExcelGoogleSheets Thank you so much for making this video! It's a great help!
I have 1 question about something I haven't been able to figure out. I own Tesla before the split (5:1) and I've bought Tesla after too, how would someone go about automating a split in our spreadsheet. I've thought about this a lot and have looked on the internet, but I can't come up with a solution with my limited knowledge of spreadsheets and couldn't find any solution on the internet.
Otherwise, I would have to go transaction by transaction making the correction and is a ton of work, especially if it happens again for any of my stocks.
@@AlexTamayo. I have the same issue and also there are some equities with the reverse split.
@@AlexTamayo. I don't know if you can automatically enter a split in the spreadsheet but you can enter it easily and manually : for TSLA, you just need to add a new transaction where you buy 4 times the number of shares you already have at a price of 0. Your number of shares, your average paid price and all the other calculations will be correct.
@@cesarduchamp2144 I know I can do that.
What I would like to know, however, is if there's anyway I can adjust my stocks for a split in all of my transactions, past, present and future. I haven't been able to figure that one out.
Very similar to one I’ve made - but soooo much better than mine! 😁
Nice Tutorial Sir. I learned something new today. I apperciate your work.
Glad to hear that
You are a genius! Thanks
Really great video tks
It's brilliant, thank you for sharing. It solves real world problems and illustrates the various techniques involved. The transactions data can be imported from our broker platform in CSV. So with only slight changes to the functions on my spreadsheet, I can get updated portfolio with hardly any manual data entry. Thanks again.
Glad it was helpful!
@@ExcelGoogleSheets 🥰Thank You so much Dear Sir it really helped me a Lot
Thank you for this tutorial, very helpful. Maybe you expand this to the cashflow also as calculation now didnt include realized gain
Awesome like always!
Thank you! Cheers!
How do exlude stocks that you have a net 0 shares in, from the portfolio. For example if you have bought and sold all shares in a stock, how do u exlude it from the portfolio list of active holdings? Thanks!
Great tracker, I'm utilizing to track across my brokerages
Great!
Great video exactly what I was looking for! I would like one that included dividends!
Dividends is only recording of profit which not effect the shares position... however bonus shares is important to be added
Great video. Perhaps you could do a script that would hide rows where the number of shares is zero, ie you have bought and sold them. It would be a great addition.
Thank you so much!
A request: Dividend yield and dividend related stuff in the portfolio
did u ever find a spreadsheet that shows dividend yield
Hope you can one day also consider expanding on this to report on trading gains/loss and capital gains/loss for tax reporting. Hence we specify a financial year end (eg 30 Jun 21) and the sheet processes both trading and capital gains/loss from the transactions data. Trading G/L is where shares are held for less than a year and Capital G/L is where they are held for more than a year before being sold. Both are normally calculated on a FIFO method. I have searched but could not find a good one.
This is amazing! Your videos have made our lives easier, I tried to incorporate dividends information but I couldn’t achieve it. I need to loop through dividend table and sum the information and again loop through the stocks again. I am not able to achieve it, can you make a video of it!
youre the greatest
Great Video Brother :) Thank you so much for Sharing. would you happen to have similar video for Options Tracking/Calculations too ?
Great video ! A video that updates from the data available from Schwab's account data would be very helpful ! For stock prices and associated covered call prices...
Hi! Thank you so much for this! Apparently, I encountered an issue while putting the UNIQUE formula on the Ticker column, when I put the formula instead it paste all of the transactions lists on the Transaction sheet. Any idea how to fix this?. I have 5 same stocks on the Transactions sheet however those 5 appears on the Ticker column on the Portfolio sheets. TIA
good stuff here!
Love ya! 🙏👍👍
This is by miles the best portfolio tracker that I came across on the web. The only improvement will be correcting the calculations for the Share Average Price Paid and the the Portfolio Average price paid. They are not straight Average of all the prices (unless same amount of shares were bot of each share). They should be calculated as weighted average price (N1*Pr1+ N2*Pr2+....+Nn**Prn)/(N1+N2+...+Nn) which requires a custom Weighed Average calculation.
weighed average video in description
Thank you so much for this step by step guide. Can you please prepare one for mutual fund portfolio too?
Excellent
Thank you so much or showing how to create a very simple stock tracker. The only thing it lacks, is reporting the accumulative profit/loss has been made from the trading activities of each of the stocks and lastly if this could be linked to a cash register that reconciles automatically the buy/sell/fees of each trade into a running total of Cash held in the account. That would make this spreadsheet very good tracker.
Awesome!
Thank you! Cheers!
Great work
Thank you! Cheers!
thank you for your time explaining all but im lost just to look at it not to mention doing it myself :D
Hello Kamil! If you need help understanding better, my mentor can help you out. You can reach out to him on his gm address
Jason Scott 9998
i love this♥ I hope i could see same tutorial on crypto
Thanks for this. Is there any way with the SORT and UNIQUE command in your first column to sort the tickers by their shareholding size (you “Current Value” column). It would then allow focus to rest on the biggest holdings and the sold holdings could be hidden at the bottom whilst still being in the total value. I couldn’t figure out a way.
Thanks for this awesome tutorial. Can we add a formula to calculate XIRR for portfolio and for a stock independently?
Excellent!! Can you explain another format with Mutual Funds and tracking Performances.
Great video. In my stock tracker, can I have a cell display YESTERDAY'S end value? What formula could be used to display that?
Hello Great sheet. Question. Is there a way to transfer to another tab the stocks that you no longer own (0 outstanding shares). First so you have a historical sheet to evaluate how they performed and also so then you have a clean looking sheet of the stocks you currently own. I currently hide them now but it's sort of a band aid solution. Just a thought. Thanks for the consideration.
This is great! Any chance you could show us how we can keep track of the money we put in? Maybe a separate spreadsheet that tallies the money that goes in and comes out? Also, is there a formula we can use to create a new column to keep the money invested separately from the profit that you take so it's easier to calculate realized gains? i.e one column for the initial money that was invested and a new column for realized gains that only calculates the profit/loss from selling stock. Thanks!
You are so awesome Bro!!! I hope also make same video for cryptocurrency.
Hello, your help is appreciated. I am trying to sort the stocks that have zero quantities (bought then sold) to the bottom but for the life of me cannot get it to work. Using sort data with header etc it comes out scattered and still in ABC order. Your help is much appreciated.....thank you
Hi, your spreadsheet is one of the best I found on the internet. Great nice work. I have 3 questions:
1) How I can track stock split or reverse split?
2) Formula seems to be not working well if I buy or sell fraction equity
3) How I can create potfolio performance in time?
These are really specific edge cases for a home-made spreadsheet to cover. You could just code your way around it instead of looking for clean solutions. For example, you could cover the impact of a 3-to-1 stock split by writing a sell-off for your stock at the old share price and writing a buy order at the new price and the new quantity. The impact is the same, although you did not really perform the trade which may skew some numbers depending on what you're tracking.
Fraction equity is another edge case but there's no part of the formula that only works around integers, so you'd have to be more specific about which part of the formula does not work correctly. If I needed an acute fix and couldn't fix the formula, I would again cheat my way out by either accepting rounding errors or by writing a fake ticker that is the required fraction of another ticker to be able to keep it in my records. But again, I don't see why the formula wouldn't work for non-integer share purchases.
Portfolio performance in time can be tracked by reading in the transactions between particular dates of time.
Hope that helps!
Thank you very much to learn Google spreadsheet. The best of all. I have been trying to find a tutorial or template to own and I paid for one spreadsheet, however not satisfied. Because I can't track the $ cost averaging.. I am watching it for few times now... Keep the great work going..no words..
Make sure you check out the second video in the description as well.
@@ExcelGoogleSheets sure. Thank you. Respect your knowledge.. I am a slow learner still watching 1st part.. will get thru the part 2. It's been more than a year I am trying a complete spreadsheet for stock tracking.. and I found the best of all.. I saw your other video how to link data from other sheets and did my best by linking the dollar cost averaging data.. thank you very much indeed
thank you so much
Thanks for watching!
Thanks a lot for the tutorial.
I have a question: Why not use ArrayFormula instead of applying the formula all the way to the bottom?
Thanks for the sheet. I have one question. I get often in the avg price paid column the value #n/a. I have the average weighted file. Do you have a solution
I have filled in all the cells in the transactions tab
Love the portfolio template , can I ask you how would I add cash to the portfolio by adding it to the transactions?
Thanks
It would be interesting to add a transaction sheet for dividends then figure that into return.
Thank you for the great work! I would like to know how to do my portfolio specifying my different accounts (RRSP, TFSA, Unregistered) because I want to sell my shares from my RRSP first and don't want to sell my shares from my TFSA or my Unregistered Account yet! Thanks
amazing
Thanks
Another question. I think it would be nice if you see only the actual stock in the portfolio tab. Now you see also the stocks when you sell them all. Is it possible the sold stocks move to a separate tab.
It is very good. But I have got a question if we sell all the google stocks the google remains in the portfolio and if we buy again the current value will be affected by the other others of the google stock right?
thank you
:)
Great
What if we sold all shares of a script in a loss? Messes up the values.. also, is there a way to calculate the realises gains? any inputs will be helpful! Thanks for this!
great video on investing journal , would be great if you can make a video on how to create a trading journal , with metrics of Batting
Avg, Stop Loss, Avg Gain/Loss, Pay of ratio. thanx and cheers
if we want to calculate avg price ( which includes buy price and sell price from transaction sheet) , without adding the avg sold column, what changes we need to make in criterion2 (">=0")?
Question: If trying to track your cryptos "current value" do you still use the =googlefinance function?
hi! Great video but was quite confused about how i will be able to keep track of the profit made from the shares i have already sold off?
If you ccompletly close out a trade will the current plrice still be there ?
Great video. Thanks you a lot. Do you know how to add option to this Tracker Spreadsheet? Will be great.
In addition to the "Percentage Change", is it possible to calculate the APR or APY? I have few stocks that I have owned for years, so it appears to be a large number. APY will give a true value on the returns.
Thanks for the vid. Isn't the avg cost = sum(price1*share_number1,price2*share_number2, ...)/total_number_shares?
Thanks for sharing this skill, request your immediate attention on how to apply FIFO to know the status as per broker data; and LIFO for getting an Individual position...