Stock Portfolio Tracker Spreadsheet - Google Sheets Template 

Welcome aboard!

Great to hear!

Great to hear!

Awesome, thank you!

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

👍

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?

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.

Glad to hear that

Glad it was helpful!

@@ExcelGoogleSheets 🥰Thank You so much Dear Sir it really helped me a Lot

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! Cheers!

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.

did u ever find a spreadsheet that shows dividend yield

Dividends is only recording of profit which not effect the shares position... however bonus shares is important to be added

weighed average video in description

Great!

Thank you! Cheers!

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! Cheers!

Thanks for watching!

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

:)

Thanks

👍

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 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.

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")?