Thanks for posting these vids, you are very good at explaining the formulas and give great examples and options. Very informative!

Woohoo. I've been waiting for something like this. Can't wait to try it out. Your brilliant. Thank you for your excellent videos.

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.

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!

@ExcelGoogleSheets

3 жыл бұрын

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.

thank you very much for the sparklines! this is one of the coolest things i've ever done in a spreadsheet

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.

thanks. I searched for other stock tracking sheets online. This one is the best!

Thanks for this! I followed it to a tee and I'm really happy with my spreadsheet!

@ExcelGoogleSheets

3 жыл бұрын

Great to hear!

Great tutorial. Easy to follow you, especially the formula creation. Thanks for the help.

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.

By far the best tutorial on google sheets for Stock market tracker.

This is the most underrated video. thank you.

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!

watching the other videos everything became clear and I solved the errors. Thank you so much. Congrats!!

Thanks so much. I have followed your step by step instructions and have been using this spreadsheet for 6 months and love it.

@ExcelGoogleSheets

2 жыл бұрын

Great to hear!

It's really good you did in this video! You rock...I wait for your next video :)

Thank you so so much!!! It's super useful, love the spreadsheet!!!!:))

You wow me! Thank you for this beautiful and informative presentation. Precise and Concise.

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!

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 :-)

Thank you very much for this tutorial! Your presentation is understanding clearly.

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!

@ExcelGoogleSheets

3 жыл бұрын

Awesome, thank you!

This was AMAZING! Thank you SO MUCH for sharing so much and giving so much value!

@ExcelGoogleSheets

Жыл бұрын

👍

Thank you man! That is the greatest guide!

Dude, you are a Ninja! Really appreciate your explanation.

it’s really great! thank you very much.

Man, this is so cool. Thank you thank you thank you thank you thank you thank you thank you thank you!!!!

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.

Thanks! this is the coolest tutorial ever

Excellent video , I had tried some others but your teaching method was excellent.

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

Love your videos. Keep it up.

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!

@ExcelGoogleSheets

3 жыл бұрын

Thank You!

@smediumlarge

3 жыл бұрын

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

Absolutly amazing! thank you for sharing it...!

Amazing... I thank you for the information. It beats going to purchase a ledger

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.

@ExcelGoogleSheets

3 жыл бұрын

Thanks, you too!

@nicholaslee4711

3 жыл бұрын

Can you suggest how I could add a leveraged trading option to this amazing sheet you’ve created? Also a live equity feature?

Great video exactly what I was looking for! I would like one that included dividends!

@sherifto7171

2 жыл бұрын

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

Very similar to one I’ve made - but soooo much better than mine! 😁

great vid, thanks so much!

Your brain is awesome bro. I was trying to do this for last 3months😃😃

@ExcelGoogleSheets

3 жыл бұрын

Glad I could help

@AlexTamayo.

3 жыл бұрын

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

@jmindl

3 жыл бұрын

@@AlexTamayo. I have the same issue and also there are some equities with the reverse split.

@cesarduchamp2144

3 жыл бұрын

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

@AlexTamayo.

3 жыл бұрын

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

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.

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.

Thanks a million for this video!

Incredible, thank you!

You are a genius! Thanks

Really great video tks

Thank you so much! A request: Dividend yield and dividend related stuff in the portfolio

@ProPrince

2 жыл бұрын

did u ever find a spreadsheet that shows dividend yield

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.

@ExcelGoogleSheets

3 жыл бұрын

Glad it was helpful!

@shetty98

2 жыл бұрын

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

Nice Tutorial Sir. I learned something new today. I apperciate your work.

@ExcelGoogleSheets

3 жыл бұрын

Glad to hear that

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

Question: If trying to track your cryptos "current value" do you still use the =googlefinance function?

Awesome like always!

@ExcelGoogleSheets

3 жыл бұрын

Thank you! Cheers!

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

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?

Great tracker, I'm utilizing to track across my brokerages

@ExcelGoogleSheets

3 жыл бұрын

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?

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.

@ExcelGoogleSheets

2 жыл бұрын

weighed average video in description

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.

If I'm adding Canadian stocks (ie. TSE: Well), what formula do I use so that the current price stays with the same row? I had adjusted the formula for that specific row using =GoogleFinance("TSE:{Symbol}","PRICE"), but once I add another stock this becomes an issue. Only solution I can think of is removing the sort functionality for the Ticker column so the rows dont adjust :( Is there another way? Thanks!

Thank you for this tutorial, very helpful. Maybe you expand this to the cashflow also as calculation now didnt include realized gain

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?

@ralphschraven339

3 жыл бұрын

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!

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.

@ryanlizotte9440

3 жыл бұрын

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)

@ExcelGoogleSheets

3 жыл бұрын

@@ryanlizotte9440 Check the description.

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.

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!

Great Video Brother :) Thank you so much for Sharing. would you happen to have similar video for Options Tracking/Calculations too ?

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!

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.

If you ccompletly close out a trade will the current plrice still be there ?

youre the greatest

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?

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

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!

thank you for your time explaining all but im lost just to look at it not to mention doing it myself :D

@Samantha-if7ts

2 жыл бұрын

Hello Kamil! If you need help understanding better, my mentor can help you out. You can reach out to him on his gm address

@Samantha-if7ts

2 жыл бұрын

Jason Scott 9998

good stuff here!

Love ya! 🙏👍👍

Hi, how would you model % of an asset where I have sold all my shares? In that case current value will be equal to zero and the '%' cell will report a -100% performance, which is not accurate.

I've been using this template for the past 4 months and it has been incredible for me. Thank you for posting it. However, one of the stocks where I used to hold a 4,500 share position recently underwent a Reverse Split position. My new share size is 300 and It messed up my formulas. My "Money Invested" shot up for some reason but it doesn't reflect my actual investment amount. I tried a few tricks like simply subtracting the amount on my transactions sheet leaving price at $0 but that doesn't fix it. I'm lost as to how to go about this. Any help would be greatly appreciated.

Thanks for this awesome tutorial. Can we add a formula to calculate XIRR for portfolio and for a stock independently?

How would you go about changing the Google Finance currency ('current price' column) to say GBP?

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

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.

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?

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

@ExcelGoogleSheets

3 жыл бұрын

Make sure you check out the second video in the description as well.

@prabukrishnamurthy3490

3 жыл бұрын

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

Love the portfolio template , can I ask you how would I add cash to the portfolio by adding it to the transactions? Thanks

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?

@ExcelGoogleSheets

3 жыл бұрын

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.

Excellent

Is there any way to add sector and industry to check on diversification ?

Been looking high and low to make Google Sheets work with OTC penny stocks. Any way to make your sheet work without rounding penny stock prices? Thanks

great work up...any chance you have a template like this for Excel? converting from sheets to excel..alot of things don't work.

Great work

@ExcelGoogleSheets

3 жыл бұрын

Thank you! Cheers!

It would be interesting to add a transaction sheet for dividends then figure that into return.

i love this♥ I hope i could see same tutorial on crypto

Great video. Thanks you a lot. Do you know how to add option to this Tracker Spreadsheet? Will be great.

You are so awesome Bro!!! I hope also make same video for cryptocurrency.