Stock Portfolio Tracker Spreadsheet - Google Sheets Template
HTML-код
- Опубликовано: 22 авг 2024
- 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.co...
Web Version: docs.google.co...
With weighted averages: docs.google.co...
Video explanation: • Google Sheets - Averag...
Learn how to use GOOGLEFINANCE function • Google Finance - Stock...
#Stock #Portfolio #Tracker #Spreadsheet #Template
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.
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.
Awesome video. I did however note that the way you do average price at the 13:00 mark does not take into account the number of shares you bought. For instance, if you buy 1 share at $10 and 10 shares at $12, the average price you paid is actually $130/11 shares = $11.82. Your formula would result in $11.
To fix it, I just added a total price for each transaction then did a SUMIFS(total price)/SUMIFS(shares if they were not negative)
@@ryanlizotte9440 Check the description.
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!
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
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.
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!
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!
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!
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!
Thanks for this! I followed it to a tee and I'm really happy with my spreadsheet!
Great to hear!
This is the most underrated video. thank you.
It would be interesting to add a transaction sheet for dividends then figure that into return.
thanks. I searched for other stock tracking sheets online. This one is the best!
You always keeps us updated with new things about spreadsheet..
Always like it.
You wow me! Thank you for this beautiful and informative presentation. Precise and Concise.
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.
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 :-)
Excellent video , I had tried some others but your teaching method was excellent.
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
thank you very much for the sparklines! this is one of the coolest things i've ever done in a spreadsheet
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?
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.
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
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.
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.
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.
watching the other videos everything became clear and I solved the errors. Thank you so much. Congrats!!
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
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?
you are an amazing teacher brother!
For the average price paid I would do a weighted average. Let's say you buy 1 share at $1000 but then buy again 10 shares at $100. The average price paid in your project would be 500, but it's clear that the real average price paid would be closer to $180
in video description
Question - if I bought 5 x GOOGL @ $500 and sold 5 x GOOGL @ $600 and then re-buy 5 x GOOGL @ $550, how to make the average to be $550? due to sold every stock before purchasing it again. How can we start new average after sold every stock to rebuy?
did you figure it out ?
Thank you very much for this tutorial! Your presentation is understanding clearly.
I always watch your videos and I always learn something new. Awesome as ever thanks!
Awesome, thank you!
Dude, you are a Ninja! Really appreciate your explanation.
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.
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.
I think investors should always put their cash to work, especially In 2024, we'll start to see more market diversification. I'm hoping to invest about $350k of my savings in stocks against next year. Hope to make millions in 2024
Man, this is so cool. Thank you thank you thank you thank you thank you thank you thank you thank you!!!!
There is a problem when selling the stock completely and then buying it again, it doesn't show the new price, nor the gain/loss of the past trasaction (the stock you completly sold before) how can this be fixed? Thanks
I was looking for the same thing and I found a new video which I believe has this formula, but not sure if the author will share it. Because I don't believe it is easy thing to calculate. Anyway, a good video though: ruclips.net/video/YwoO31z7B6s/видео.html
@@topalano55 You're right he does track that quite well. That would be a good sheet to have but it's going to take a while to get to 1000. I do like this one and hope he keeps improving it.
You could create a new column for realized gain/loss and update that as you exit positions
Great tutorial. Easy to follow you, especially the formula creation. Thanks for the help.
Amazing... I thank you for the information. It beats going to purchase a ledger
Great tracker, I'm utilizing to track across my brokerages
Great!
Thanks for your video. There is an error in Avg. Price Paid. The correct amount should be Money Invested/Shares. Another error is in Money Invested. Your formula amount is different when adding values one by one. I compared it with items from my spreadsheet and in all i found the same errors.
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
Nice Tutorial Sir. I learned something new today. I apperciate your work.
Glad to hear that
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?
It's really good you did in this video! You rock...I wait for your next video :)
This is an excellent tutorial and has helped me a lot to build a similar kind of thing in excel. Correct me if I am wrong but there appears to be one issue with the way how this table works that I spotted:
Imagine you make the following trades (ticker/price/no of shares):
AAPL $132.87 0.5
AAPL $109.99 0.3
AAPL $105.19 0.6
AAPL $115.33 -1.4
AAPL $128.12 1
AAPL $128.92 -0.5
AAPL $138.08 -0.5
AAPL $137.45 1
At this point, we only own one share at $137.45. But the value you get in the table under the average price paid is $122.72 which is inaccurate since it does not reflect the real value of the current holdings. Is there any way to fix this? Thanks
This was AMAZING! Thank you SO MUCH for sharing so much and giving so much value!
👍
Your "Average Price Paid" column is completely wrong. The way you have it set up, if I was to buy 100 shares at $1 and 1 share at $100, your formula would show an average price of $50.50. Obviously, that is not correct. The real Average Price Paid is (100*1 + 1*100)/101 = 1.98 The Average Price Paid needs to be a SUMPRODUCT!
Sure, you can change it to weighted average instead, which is what you're referring to.
Here;is a video ruclips.net/video/aOUgMraeC9Q/видео.html
@@ExcelGoogleSheets no it still doesn't work, if I buy 3 aapl then sell 3 aapl, then 1 year later later buy 3 more, it will show the incorrect cost per share, everyone keeps posting tutorials and they all are wrong because of the above
@@TravisKlein1 You can keep complaining, buy It's impossible to calculate this with a basic formula. You would have to setup scripts to keep track of shares inventory at the time with prices bought and sold. This would require a lot of data stored, so using a database instead of a spreadsheet would make more sense.
A spreadsheet like this should be fine for a small operation, but if you want commercially viable system then it would require many hours to build one, instead of 30-40 minutes.
@@TravisKlein1 Also it's arguable if the accurate way to calculate average price is to only use prices for shares you currently hold.
Thank you so so much!!! It's super useful, love the spreadsheet!!!!:))
great vid, thanks so much!
You are a genius! Thanks
Love your videos. Keep it up.
There is a minor problem if you look at your example. Amazon now shows a negative amount of money invested and the percent change has vanished. This happens when you sell an amount of shares for a greater value than you paid for them. IE a share goes up 300% and you sell half.
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...
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 for this step by step guide. Can you please prepare one for mutual fund portfolio too?
Thanks! this is the coolest tutorial ever
If you ccompletly close out a trade will the current plrice still be there ?
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...
Love everything about this except for how the negative percent change is handled. Has anyone found a more elegant way to handle this.
I found my answer moments later. All I had to do was add the function ABS in front of the IF function. Now the percentage shows correctly for the remaining shares. Thanks again for sharing this portfolio. Great job!
Really great video tks
it’s really great! thank you very much.
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")?
Thank you man! That is the greatest guide!
Love the portfolio template , can I ask you how would I add cash to the portfolio by adding it to the transactions?
Thanks
Excellent Idea on Tracker.
We need a, How much my wife spends on shopping Tracker in Python and Tensorflow app
:)
Thank you for this tutorial, very helpful. Maybe you expand this to the cashflow also as calculation now didnt include realized gain
Absolutly amazing! thank you for sharing it...!
Thanks a million for this video!
Very similar to one I’ve made - but soooo much better than mine! 😁
Great Video Brother :) Thank you so much for Sharing. would you happen to have similar video for Options Tracking/Calculations too ?
Thanks for the vid. Isn't the avg cost = sum(price1*share_number1,price2*share_number2, ...)/total_number_shares?
Incredible, thank you!
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 the best efficient spreadsheet I've come across. Great explanation. Please keep up the good work. I'm still trying to figure out if I get a dividend of a stock how will I adjust the cost basis?
👍
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!
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
Big Tech Stocks, Mergers, and Rare Earth, and Payment Stocks like Square. And Military Stocks, and Silver Stocks.
i love this♥ I hope i could see same tutorial on crypto
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
Excellent
thank you
:)
You are so awesome Bro!!! I hope also make same video for cryptocurrency.
good stuff here!
Thank you for this good video. I want you to as a question. I saw in the previous videos you used array formula for different situation. Why an "Arrayformula" does not work for this situation? Why we have to drag down all the way?
I could probably covert a lot of those to array formulas, but not all of them. Not every formula can be converted to an array formula that will fill down.
Hello, great sheet, great work! Question...how do you enter Reinvestment Dividend Distribution? If I enter fraction of stock and it's value it seems to degradate the avg paid. Thanks....
oh also, how about Stock Splits?
thx
I have the same issue. I need to track fraction buy and sell and also split and reverse split.
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
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!
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
Thanks for this awesome tutorial. Can we add a formula to calculate XIRR for portfolio and for a stock independently?
Great job! I think your chart is backwards though since it start from latest to oldest. You should change the data of the price that you downloaded from oldest to latest and your chart should look fine.
It's possible. The point of the video is to teach, so if you understand what's happening than I'm gonna consider I did my job :)
I am interested to import data via API from Fidelity & TastyWorks for daily transactions which will automate the portfolio to current within 20 mins. Also when a stock is closed, remove it from the portfolio to a history sheet.