@@ThinkStocks Can you please do one that gets crypto data? with this exact set up? This is Essentially!! Super dynamic and makes sense but im having trouble with it maybe because of crypto
First of all, this whole economic chaos was powered by optimism that the FED is done with hiking interest rates. Now that interest rate crash is the situation, where do we go from here? How would you advise I safely allocate $250k funds at this point?
The market is volatile at this time, But doesn't the Federal Reserve's monetary policy and low interest rates contribute to the current valuations? hence I will advice you get yourself a financial advisor that can provide you with entry and exit points on the share/ETF you focus on.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my CFP are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
’Sonya Lee Mitchell is the licensed advisor I use. Just research the name. You’d find necessary details to work with a correspondence to set up an appointment.
Thank you so much for your helpful tip! I was able to verify the person and book a call session with her. She seems very proficient and I'm really grateful for your guidance
Acquiring a stock is easy, but buying the right stock without a time-tested strategy is incredibly hard. Hence what are the best stocks to buy now or put on a watchlist? I’ve been trying to grow my portfolio of $160K for some time now, but my major challenge is not knowing the best entry and exit strategies. I would greatly appreciate any suggestions.
Investors should be cautious about their exposure and be wary of new buys, especially during inflation. Such high yields in this recession are only possible under the supervision of a professional or trusted advisor.
While this provide an actual strategy on investment, on how you buy your stocks. After buying shares for just over 10 years, i'm struggling to make gains. How do i adjust or revamp my $2M portfolio? should i consider some defensive investments?
In this current unstable markets, It is advisable to diversify while retaining 70-80% in secure investments. looking at the worth of your portfolio, you should consider financial advisory.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my advisr are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
She goes by ‘’Jessica Annabelle Rupp’ I suggest you look her up. To be honest, I almost didn't buy the idea of letting someone handle growing my finance, but so glad I did.
The best video I could find about portfolio tracking. I was thinking about getting a portfolio tracker app, but the monthly subscription all seems to be a bit over-priced, it works in the long run that you have your own sheet.
This is fantastic. Thank you for your effort. I made a small change for calculating the purchasing. You just average and weight the bought price of purchasing a stock. But once you sell a portion of the shares, the purchasing cost of the rest will be changed. So I think the sold price of the stock has to be considered as well. Therefore I made a change like this: sumproduct(filter(Data!E:E,Data!B:B=A3),filter(Data!D:D,Data!B:B=A3))/sum(filter(Data!D:D,Data!B:B=A3))
thank you for sharing this code. this way the average cost will account in the profit or lost from the previous selling into the total cost, am i right ?
Every thing is working well 👍. I have just 2 Queries to resolve. 1. Shifting realised to unrealised 2. Purchase Price Shifting While both if these queries are interconnected Any of the query need to be resolved. Rest automatically will get resolved.
Thank you for sharing this. it is an excellent tool. One comment: if you sell all the stocks of "X" and buy the same stock again, your average weighted cost is no longer accurate. Same goes if the stock had undergone split or reverse splits. A simple solution for this would be to add one more column in the "History" tab and add a condition that tell the Purchase formula which buys to consider. I am sure you may have slicker scripts for that though.
That moment where you went through the entire video non-stop pausing looking at the coding and formulas to find out that he had most in the description. Great video you've earned my Sub :)
Thank you so much for the awesome video! I'm an excel user and have actually never heard of Google sheets until now. I really like this software. You explained this in a simple-to-follow format. Christ be with you!
This is a great coach to track our portfolio. Spent about 4hrs to work on framing my portfolio. I enjoyed it very well and its a great learning. Thanks for educating.
Very Nice Video, Just one thing though when you calculate the cost-basis of a symbol, the formula is not taking into account symbol being traded multiple times. For Ex - If i buy PYPL at 10 and then sell at 12 and then buy at 15 and then buy at 20, considering I bought 1 share each time.. the cost basis would be 17.5, whereas in the way you did ... it will come out to be 15. so just a minor suggestion. Overall I loved this idea of using so much automation in your spreadsheet... I am kind of old school manually inputting values throughout the day.. Casual trader here .. so just 2-3 trades a month is what I can manage.
Guy you need to teach a class!!! That was the most detailed, clear, & accurate tutorial I'll EVER watched!!! This helped me more than you will ever know! I just started investing and this will help me keep a better track of it compared to putting it on paper ANY DAY! THANK YOU THANK YOU THANK YOU for the time you spent to teach us this!!! You have definitely made my life easier.
Looking forward to your next video! Just wondering whether you can introduce a column to the sheet showing annualised returns, gains, losses, etc., & return over the past 12 months, quarter... I think it'll be useful especially if you hold the stocks for a longer term. Thank you.
I really cannot thank you enough for this STELLAR VIDEO!!! I learned more about Google Sheets from this one video than I have in YEARS!! You are providing an extremely valuable service, thank you! You have a new subscriber!
Hi thinkstocks thanks for the super detailed tutorial! Learnt a lot and I really appreciate it. While playing around with the sheets, I think I may have encountered an issue with the weighted average formula. The formula you used was =if(isblank(A3),"",AVERAGE.WEIGHTED(FILTER(History!E:E,History!B:B=A3,History!D:D▶0),FILTER(History!D:D,History!B:B=A3,History!D:D▶0))) but this also accounts for past positions bought but subsequently sold in the history. For example, let’s say these transactions were carried out in order: 1. Bought 1 AAPL @$100 2. Sold 1 AAPL @$105 3. Bought 1 AAPL @$110 Using the weighted average formula, purchase will be $105 per share. However the real average should be $110 since the first share was already sold off and shouldn’t be counted in the weighted average. Because of this the realised gain row in the dashboard will also be off, showing $0 gain when it should be $5. Is there any workaround for this? Thanks :)
Hey Justin, glad you liked my video, and yes! It took some time to figure out how to fix it but this Friday I will be releasing a video with the solution. Its a pretty lengthy code but it work great to solve this problem!
First of all, thanks for showing how to build your own porfolio in google sheets. I have written the script and had problems getting it to work. when writing down i saw my flaw and it works. Great thanks!
OMG, I did the full thing from start to finish, It took me 6 hours but I did it! thank you so much, I will like share and subscribe and I will aslo join the Patrean just to support the channel, This Video is awesome and so in dept! thank you so much!
Thank you, truly one of the more comprehended portfolio tutorial video available to date. Really appreciate it as I am just starting with investing with multiple brokers. This will be perfect to have everything in one place
Noticed that the weighted average function used for avg price/share doesn't work for when you sell out of a position and then buy back in - because it's adding the price from when you first owned the share to what you own now. Thoughts on a work around for this?
Great tutorial for someone whose getting started managing portfolio in a customized way! One thing though: the way you calculate average cost doesn't count for sells at all, which isn't always correct. Should use one of FIFO, LIFO or high/low cost.
Very cool + helpful video!! One question - Realized Gains: =sumif(History!C:C,"Sell",History!F:F)-sumif(History!C:C,"Buy",History!F:F)-sumif(History!C:C,"DRIP",History!F:F)+sum(Positions!G3:G30) - If you also want to account for dividends and "do it for both" - how would that line look like?
Buying stocks of companies has reported to have increased earnings for beginners but other favourable developments has to be considered to anticipate desired trading outcomes.
I recently started looking into penny stocks, i only invest small sums. But i'm interested to learn the trend factor of making huge gains on penny and tesla stocks
Investors should be cautious when it comes to trading stocks because the stock market is riddled with disquiet, you need proper education on the market.
@@owenmartinez5577 The ability of making huge gains on penny stocks is a technique rendered by professionals, reason why i make use of topmost trading expert service to pick out valuable stocks for me as i earn massively in the process
The stock market has been a financial escape plan for investors around the globe but beginners will never understand how profitable the stock market is if they are not investing in the market
@@raulandre677 I disagree, stock trading is highly profitable to both non and full investors, the profitability of stocks mainly depends on your ability to take advantage of the market because most investors make gains in expense of the loss of others
There is a flaw on your code... When you close a position in a stock and (say a month after) buy it again, it will consider your entire purchase history, and won't reflect your actual average buying price. You should also calculate average prices on the gs algorithm, and make the 'total' variable return this column too.
@@raunvs In fact this is quite simple. Just add a new object (say avgs{}) and put the average prices there by ticket. When a position comes to zero then make this avg[position] equals zero too. But caution to not test for lower or equal than zero on a position, because if there is a sell entry and a buy entry on the same day, and depending on the order, it may be negative in some point of calculation...
This is fantastic! @ThinkStocks, did you happen to do an update to manage track options activity? I sell options to lower my basis and it would be swell to integrate that into the sheet to more accurately track everything and manage cash positions.
Thank you for such a great walkthrough. I created the portfolio sheet with some additional flavour (mixing in both USD and CAD and creating a baseline CAD totals) as well as scouring yahoo finance for Sectors so I can have a sector diversification chart on my dashboard. It was quite a bit of work to makes sure all the data was inputted properly, but am really happy with the end result, can't wait for Tuesday to make some purchases just to add it onto the portfolio tracker!
Hello. If its not too much trouble, could you explain how you converted the usd to cad? I found the function to convert but can't figure out the coding to get it to know if its a us stock or a cad stock.
@@derekd1997 =if(isblank(A3),"",googlefinance(A3,"Currency")) .... where A3 is the ticker, note for US listings, just ticker, for TSX, must be proceeded by TSE:___.
Thanks - If possible please consider performance sheet where we can see annualized, cumulative and annual and or quarterly returns where data can be compared with S&P and DJIA; also it would be nice to have designated location of each transaction meaning automatically going to different portfolio. i.e 401K , ROTH etc.
You can add a splicer to select different accounts. Idk how to graph quarterly returns though, I got as far as writing a formula to label each entry by quarter based on the date of the transaction, but couldn’t figure out how to calculate the values for different funds in intervals.
There is an edge case in your code. You need to ignore dividends when grabbing Ticker for Breakdown. Otherwise, when you sell everything, it will treat dividends as you still hold stocks.
Getting # Error! on the Positions tab after the MyPortfolio, and error on the History Total. So I'm stuck 20 minutes in the video. Already checked several times the coding script. Edit: use a ; instead of a comma: =MyPortfolio(History!B3:B1000;History!D3:D1000); then it works The same for the total
Great video! Super helpful but I have a question, I think the weighted average formula didn't account for the sell amount of shares, so when we have stock dividends, the price is not calculated correctly
yes true, this gives a mistake in average purchase in postions tab. It is because the purchases price looks for all purchases to cal weighted avg, but ignores the fact that we could have sold or squared off our previous purchase and starting a fresh on the same stock
This Portfolio Tracker is amazing, but the issue I facing that when I put the Dividend & DRIP under any Security Symbol with Quantity on the History tab is also increased my Quantity of shares on the Positions tab also... For that, it's affecting the Purchase... Can you please elaborate that how can I put the Dividend & DRIP of any Security under the History tab? Thank You!!
I'm wondering the same thing for Dividend. For DRIP - you need to enter the fractional share amount that was purchased, and your share count will be correct, but for non-DRIP Dividends, it's altering the share count when it shouldn't. Does it need to be labeled as "Cash" instead of a ticker because it's like a different type of deposit?
@@adamwentworth I am having the same issue. I thought I may have been entering the DRIP wrong. I wonder if there is a code/formula fix for this or if it's just a matter of logging it into History differently.
I think I may have figured this out. If you add +sumif(History!C:C,"Dividend",History!F:F)-sumif(History!C:C,"DRIP",History!F:F) to the end of the formula in the cell for Cash, and then make sure you enter dividend payments and DRIP payments separately in the History, it should add the dividend payment to cash and then subtract it while still correctly changing your share amount on the Positions tab. I THINK it also correctly updates the dollar cost average amount in the Purchases tab as well, but I have not tested that yet.
Same here, I'm trying to find a solution. When you check the video at 36:47, you can see how he registered the DRIP in history, Idk how he calculated the quantity. I tried to do something different using dividend yield and price but it didn't work :(
Hi, thank you for sharing your wonderful portfolio tracker. I am still working my way through it. Very comprehensive instruction. Question: For the calculation Average Purchase Price in the Position sheet, it doesn't seem to take into consideration if a specific stocks are partially sold. Example: 1 jan 2021 MSFT Buy 200 $100 1 feb 2021 MSFT Sell -200 $120 then 1 mar 2021 MSFT Buy 300 $130 The Average purchase price should only take the purchase on 1 Mar and ignore the purchase on 1 Jan since it has already been sold in Feb. but it seems the formula still takes both Jan and Mar into the averaging. Not sure if I am correct.
Very comprehensive tutorial, great job. Still I am facing the following issue: Let's say I had a stock that I completely sold at some point resulting in a realized gain. then, at a later stage I bought it again - the price now will be averaged with the old stocks, which I don't want to. How did you solved this one?
This help a lot. Thank you so much for sharing. Your effort of doing this video for more than an hour is super appreciated. I liked, subscribed and didn’t skip ads to give back the favor. ❤️
This was an excellent and very helpful video! However, after creating the tracker for my portfolio, I found it was rendering inaccurate results. I dug into it and found that the method to calculate purchase price (starts at around 22:26) will lead to an incorrect value for anyone who has sold the stock and again bought it for a different price. For example, someone buys a stock at $100 and then sells it for $150. Now, again he is buying the same stock at $120. In this instance, his average purchase price calculated by the given method will be an average of $100 and $120 = $110, but his actual average purchase price should be $120. Can anyone suggest a way to correct this?
You can add a check for cell C to only contain Buy orders by adding History!C:C="BUY" to the formula like this: =IF(ISBLANK(A3),"",AVERAGE.WEIGHTED(FILTER(History!E:E,History!B:B=A3,History!D:D>0,History!C:C="BUY"),FILTER(History!D:D,History!B:B=A3,History!D:D>0,History!C:C="BUY"))) This goes in the purchase category, Column C in the Positions tab :)
issue I am having is when making the chart on breakdown sheet. When highlighting the value from A3 to A100 it doesn't allow me to click ok. It shows Invalid Type.
First of all Great tutorial. Thanks A lot! Only Question i have is: How Do i make Cash deposits in the History tab not to appear in the Positions tab, it messes up the calculations on the Dashboard. because what happens when i input deposits, it refers to it as a stock and counts it with all the other stocks in the Positions Tab
Thanks for this video! Very helpful! Is there any way to sort the Positions tab by Alphabetical Order of the stock ticker? I would love it if it updated automatically when buying or selling new positions.
Wow! Two (2) thumbs up to you for bringing this video to the masses. I hung in there from start to finish, learned a plenty and now I've got my own tracker. Much appreciate your efforts...Stay Safe!
I'm getting a #REF! error when I enter the function at 18:26. I have double checked my code for any errors, but it is identical to his. Can someone help with this?
I get Error: "ReferenceError: values is not defined (line 16)." on my =MyPortfolio(History!B3:B1000;History!D3:D1000). Any suggestions? I check the Script Ln 16 = "sums[t] = Number(values[i])" am I missing something still?
Loved the vid took me a while to get it right had to go to dc for the myportfolio but all worked great. Keep up the good work. Subbed and liked. Ps i would love to incorporate crypto any change you will look in to that?
Great video. One questions. When I include stocks from other countries/ exchanges, how do I incorporate currency conversion (i.e. foreign currency to USD - using live exchange rates) into the spreadsheet so that the prices get updated automatically? Thanks heaps.
Great video. Just one more suggestion, everytime you buy and sell equity, we pay the exchange some charges, would suggest you to include a column for same, so that these charges can be accounted for.
Thank you so much for taking the time to walk us through this process. The code to copy and paste made creating the sheet super easy! You are a gentleman and a scholar!!!!!
Same, but check @Oliver Mark comment, he said to put it like this at 18:19 use a ; instead of a comma: =MyPortfolio(History!B3:B1000;History!D3:D1000); then it works edit: and in the history total use the ; as well instead of the two commas
Make sure to subscribe to my channel for more awesome content like this! I post new videos every single week!
Great video but one thing google finance lacks of is getting data from non-us firms.
Don't worry my friend. Next Friday I'll have a video up that shows how to pull data for international stocks!
@@ThinkStocks great! looking forward to it!
@@ThinkStocks Any chance you could answer my query below. Could i send my sheet to you please?
@@ThinkStocks Can you please do one that gets crypto data? with this exact set up?
This is Essentially!! Super dynamic and makes sense but im having trouble with it maybe because of crypto
First of all, this whole economic chaos was powered by optimism that the FED is done with hiking interest rates. Now that interest rate crash is the situation, where do we go from here? How would you advise I safely allocate $250k funds at this point?
The market is volatile at this time, But doesn't the Federal Reserve's monetary policy and low interest rates contribute to the current valuations? hence I will advice you get yourself a financial advisor that can provide you with entry and exit points on the share/ETF you focus on.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my CFP are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
This sounds interesting. My portfolio is in the red. Can you recommend your analyst, please?
’Sonya Lee Mitchell is the licensed advisor I use. Just research the name. You’d find necessary details to work with a correspondence to set up an appointment.
Thank you so much for your helpful tip! I was able to verify the person and book a call session with her. She seems very proficient and I'm really grateful for your guidance
Acquiring a stock is easy, but buying the right stock without a time-tested strategy is incredibly hard. Hence what are the best stocks to buy now or put on a watchlist? I’ve been trying to grow my portfolio of $160K for some time now, but my major challenge is not knowing the best entry and exit strategies. I would greatly appreciate any suggestions.
Investors should be cautious about their exposure and be wary of new buys, especially during inflation. Such high yields in this recession are only possible under the supervision of a professional or trusted advisor.
While this provide an actual strategy on investment, on how you buy your stocks. After buying shares for just over 10 years, i'm struggling to make gains. How do i adjust or revamp my $2M portfolio? should i consider some defensive investments?
In this current unstable markets, It is advisable to diversify while retaining 70-80% in secure investments. looking at the worth of your portfolio, you should consider financial advisory.
Agreed, my portfolio is well-matched for every market season yielding 85% from early last year to date. I and my advisr are working on a 7 figure ballpark goal, tho this could take another year. IMO, financial advisors are the most sought-after professionals after doctors.
She goes by ‘’Jessica Annabelle Rupp’ I suggest you look her up. To be honest, I almost didn't buy the idea of letting someone handle growing my finance, but so glad I did.
The best video I could find about portfolio tracking. I was thinking about getting a portfolio tracker app, but the monthly subscription all seems to be a bit over-priced, it works in the long run that you have your own sheet.
This is fantastic. Thank you for your effort.
I made a small change for calculating the purchasing. You just average and weight the bought price of purchasing a stock. But once you sell a portion of the shares, the purchasing cost of the rest will be changed. So I think the sold price of the stock has to be considered as well. Therefore I made a change like this:
sumproduct(filter(Data!E:E,Data!B:B=A3),filter(Data!D:D,Data!B:B=A3))/sum(filter(Data!D:D,Data!B:B=A3))
thank you for sharing this code. this way the average cost will account in the profit or lost from the previous selling into the total cost, am i right ?
Hi, thank you for your code, can you please explain where I can insert it? I'm new to this.
Every thing is working well 👍.
I have just 2 Queries to resolve.
1. Shifting realised to unrealised
2. Purchase Price Shifting
While both if these queries are interconnected
Any of the query need to be resolved.
Rest automatically will get resolved.
Great tutorial! Your code could be optimized, here's an improved version:
function MyPortfolio(symbols, values) {
const sums = symbols.reduce((map, ticker, i) => {
ticker = ticker.toString();
if (map[ticker]) {
map[ticker] += Number(values[i]);
} else {
map[ticker] = Number(values[i]);
}
return map;
}, {});
return Object.entries(sums);
}
Thanks man! And this is amazing!
Hey Man, thank you so much.
Hey! thanks so much, would you know how to incorporate the " if(sums[ticker]>0){" line into this code so that it doesnt include any stocks I've sold?
Thank you! This is awesome!
this is cool
I’m an excel nerd and your tracker is beautiful! I can’t wait to improve what I’ve been working on with what you’re showing! Great video :)
Thank you!
Hello, I see you are really like excel. I have a small request, can you help me with this sheet as I'm stuck at the place of the position?
Thank you for sharing this. it is an excellent tool. One comment: if you sell all the stocks of "X" and buy the same stock again, your average weighted cost is no longer accurate. Same goes if the stock had undergone split or reverse splits. A simple solution for this would be to add one more column in the "History" tab and add a condition that tell the Purchase formula which buys to consider. I am sure you may have slicker scripts for that though.
Facing the same problem here. Cant figure out how to fix it.
That moment where you went through the entire video non-stop pausing looking at the coding and formulas to find out that he had most in the description. Great video you've earned my Sub :)
Thank you!
Thank you so much for the awesome video! I'm an excel user and have actually never heard of Google sheets until now. I really like this software. You explained this in a simple-to-follow format. Christ be with you!
This is a great coach to track our portfolio. Spent about 4hrs to work on framing my portfolio. I enjoyed it very well and its a great learning. Thanks for educating.
Very Nice Video, Just one thing though when you calculate the cost-basis of a symbol, the formula is not taking into account symbol being traded multiple times. For Ex - If i buy PYPL at 10 and then sell at 12 and then buy at 15 and then buy at 20, considering I bought 1 share each time.. the cost basis would be 17.5, whereas in the way you did ... it will come out to be 15. so just a minor suggestion.
Overall I loved this idea of using so much automation in your spreadsheet... I am kind of old school manually inputting values throughout the day.. Casual trader here .. so just 2-3 trades a month is what I can manage.
This is absolutely SUPERB!!!!!Thank you so much. This is way better than most of the apps out there
Wow, you even gave us the functions/formulas in the description. Great work bud.
Guy you need to teach a class!!! That was the most detailed, clear, & accurate tutorial I'll EVER watched!!! This helped me more than you will ever know! I just started investing and this will help me keep a better track of it compared to putting it on paper ANY DAY! THANK YOU THANK YOU THANK YOU for the time you spent to teach us this!!! You have definitely made my life easier.
Who would dare! not smash that like button for this banger of video. This man has me getting organized!
Coming in with the fire comments again🔥
DAM! This is the most complete portfolio sheet I have ever seen. Well done
Looking forward to your next video! Just wondering whether you can introduce a column to the sheet showing annualised returns, gains, losses, etc., & return over the past 12 months, quarter... I think it'll be useful especially if you hold the stocks for a longer term. Thank you.
I really cannot thank you enough for this STELLAR VIDEO!!! I learned more about Google Sheets from this one video than I have in YEARS!! You are providing an extremely valuable service, thank you! You have a new subscriber!
Glad you enjoyed it!!
I want to press the like button more than once. Great stuff!
Hi thinkstocks thanks for the super detailed tutorial! Learnt a lot and I really appreciate it. While playing around with the sheets, I think I may have encountered an issue with the weighted average formula. The formula you used was =if(isblank(A3),"",AVERAGE.WEIGHTED(FILTER(History!E:E,History!B:B=A3,History!D:D▶0),FILTER(History!D:D,History!B:B=A3,History!D:D▶0))) but this also accounts for past positions bought but subsequently sold in the history. For example, let’s say these transactions were carried out in order:
1. Bought 1 AAPL @$100
2. Sold 1 AAPL @$105
3. Bought 1 AAPL @$110
Using the weighted average formula, purchase will be $105 per share. However the real average should be $110 since the first share was already sold off and shouldn’t be counted in the weighted average. Because of this the realised gain row in the dashboard will also be off, showing $0 gain when it should be $5. Is there any workaround for this? Thanks :)
Hey Justin, glad you liked my video, and yes! It took some time to figure out how to fix it but this Friday I will be releasing a video with the solution. Its a pretty lengthy code but it work great to solve this problem!
@@ThinkStocks thanks a lot, looking forward to your video!
Thank you so much for creating this! Best tutorial so far.
First of all, thanks for showing how to build your own porfolio in google sheets. I have written the script and had problems getting it to work. when writing down i saw my flaw and it works. Great thanks!
Thats awesome to hear and glad you liked my video!
This was an amazing tutorial, thank you so much for taking the time and sharing this!
OMG, I did the full thing from start to finish, It took me 6 hours but I did it! thank you so much, I will like share and subscribe and I will aslo join the Patrean just to support the channel, This Video is awesome and so in dept! thank you so much!
Thank you, truly one of the more comprehended portfolio tutorial video available to date. Really appreciate it as I am just starting with investing with multiple brokers. This will be perfect to have everything in one place
Thanks so much for doing this. I had no knowledge about Sheets and now I got confidence and idea to add more metrics that I want.
Noticed that the weighted average function used for avg price/share doesn't work for when you sell out of a position and then buy back in - because it's adding the price from when you first owned the share to what you own now. Thoughts on a work around for this?
Exactly. I need a solution for this as well.
Has anyone figured out the solution to this?
got any solution for this?
did u got any solution
Very nice portafolio tracker and really useful step by step explanation, thank you
Great tutorial for someone whose getting started managing portfolio in a customized way! One thing though: the way you calculate average cost doesn't count for sells at all, which isn't always correct. Should use one of FIFO, LIFO or high/low cost.
Thanks Sean! And don't worry, I solved this problem and will be putting up a tutorial on this soon! The tutorial will show you the FIFO method
Thanks for the tutorial, I have same issue "the average price doesn't eliminate sold stocks price" . Keep us posted for the new tutorial.
Cant find the tutorial! Its maddening
This Friday at 9am EST the FIFO video is coming out!
@@ThinkStocks hey, could you please link the fifo video?
Thanks for your work, this is excellent stuff
Very cool + helpful video!! One question - Realized Gains: =sumif(History!C:C,"Sell",History!F:F)-sumif(History!C:C,"Buy",History!F:F)-sumif(History!C:C,"DRIP",History!F:F)+sum(Positions!G3:G30) - If you also want to account for dividends and "do it for both" - how would that line look like?
Buying stocks of companies has reported to have increased earnings for beginners but other favourable developments has to be considered to anticipate desired trading outcomes.
I recently started looking into penny stocks, i only invest small sums. But i'm interested to learn the trend factor of making huge gains on penny and tesla stocks
Investors should be cautious when it comes to trading stocks because the stock market is riddled with disquiet, you need proper education on the market.
@@owenmartinez5577 The ability of making huge gains on penny stocks is a technique rendered by professionals, reason why i make use of topmost trading expert service to pick out valuable stocks for me as i earn massively in the process
The stock market has been a financial escape plan for investors around the globe but beginners will never understand how profitable the stock market is if they are not investing in the market
@@raulandre677
I disagree, stock trading is highly profitable to both non and full investors, the profitability of stocks mainly depends on your ability to take advantage of the market because most investors make gains in expense of the loss of others
This was such an excellent tutorial. Thank you for the wonderful share!
There is a flaw on your code... When you close a position in a stock and (say a month after) buy it again, it will consider your entire purchase history, and won't reflect your actual average buying price.
You should also calculate average prices on the gs algorithm, and make the 'total' variable return this column too.
Good catch there.. I have been thinking about this and not sure how to correct this. Any ideas?
Don't worry guys I have a a solution to this error, video is coming out soon!
@@raunvs In fact this is quite simple. Just add a new object (say avgs{}) and put the average prices there by ticket. When a position comes to zero then make this avg[position] equals zero too.
But caution to not test for lower or equal than zero on a position, because if there is a sell entry and a buy entry on the same day, and depending on the order, it may be negative in some point of calculation...
Or you can make another function, just to calculate the averages...
@@ThinkStocks is there any solution came up for this???
Thank you very much for the video. It is beautiful explained !!
This is fantastic! @ThinkStocks, did you happen to do an update to manage track options activity? I sell options to lower my basis and it would be swell to integrate that into the sheet to more accurately track everything and manage cash positions.
Very Useful for any Stock Traders, Thanks a lot to give me the video as a wonderful gift.
Awesome spreadsheet man 🔥🔥🔥 I love creating my own views with Sheets or Websites to interpret the stock mkt data I care about.
Loved the video. Learned so much in a short period of time. Appreciate the work.
Thank you for such a great walkthrough. I created the portfolio sheet with some additional flavour (mixing in both USD and CAD and creating a baseline CAD totals) as well as scouring yahoo finance for Sectors so I can have a sector diversification chart on my dashboard. It was quite a bit of work to makes sure all the data was inputted properly, but am really happy with the end result, can't wait for Tuesday to make some purchases just to add it onto the portfolio tracker!
Hello. If its not too much trouble, could you explain how you converted the usd to cad? I found the function to convert but can't figure out the coding to get it to know if its a us stock or a cad stock.
@@derekd1997 =if(isblank(A3),"",googlefinance(A3,"Currency")) .... where A3 is the ticker, note for US listings, just ticker, for TSX, must be proceeded by TSE:___.
Them simply do a if (or sumif) statement looking for CAD and multiplying the value using GOOGLEFINANCE("CURRENCY:USDCAD").
This is the video that I have been looking for a long time. Thanks man!
This is awesome. Will be interested in how you'd structure a tracker for a portfolio with assets in different countries - UK, AU, NZ, US.
have you figured this out?
Incredible attention to detail on video and formulas in description. Thanks for this.
Thanks - If possible please consider performance sheet where we can see annualized, cumulative and annual and or quarterly returns where data can be compared with S&P and DJIA; also it would be nice to have designated location of each transaction meaning automatically going to different portfolio. i.e 401K , ROTH etc.
Yes, this is what I am looking for as well
You can add a splicer to select different accounts. Idk how to graph quarterly returns though, I got as far as writing a formula to label each entry by quarter based on the date of the transaction, but couldn’t figure out how to calculate the values for different funds in intervals.
Great video man, also for the functions and graphs instead of Doing B3:B1000 or whatnot, B3:B will select the entire column!
There is an edge case in your code. You need to ignore dividends when grabbing Ticker for Breakdown. Otherwise, when you sell everything, it will treat dividends as you still hold stocks.
Hello, do you know how to do this?
Bro, this is theeee best video I've ever watched on TH-cam thanks a lot👍💯
Glad you think so!
Getting # Error! on the Positions tab after the MyPortfolio, and error on the History Total. So I'm stuck 20 minutes in the video. Already checked several times the coding script.
Edit: use a ; instead of a comma: =MyPortfolio(History!B3:B1000;History!D3:D1000); then it works
The same for the total
Try ";" Instead of ","
i had same problem but whenever i change a comma to ; it stills error and whenever i check it still comma
Same problem but whenever I use ; instead of a comma I still have the error. How can I do?
@@diegolauricella5650 having same problem here :/ still says unknown function
@@alvintjw123 same here
This was so helpful!! I've been wanting to create something like this for myself to manage my multiple accounts. Thank you for you time!
Great work! Thanks! How about multi-portfolio? For example, I have 3 broker accounts
i wanted to know the same thing
Yes I have the same question!
Such an Amazing step by step video. GREAT WORK. Exactly what I was looking for.
Does this code worked?
@@MaxTheCopperHusky oh yes it did.
Thank you for the great video,, when I add dividend how do I remove it from the portfolio position sheet?
Input as Cash
This guy is too genius for us.
Is there a way to do this with options as well?
Dude, you have one of the best content about google sheets / finance of TH-cam. No kidding!!!! Subscribed.
Great video! Super helpful but I have a question, I think the weighted average formula didn't account for the sell amount of shares, so when we have stock dividends, the price is not calculated correctly
did we figure this out? cause im having the same problem now haha
yes true, this gives a mistake in average purchase in postions tab. It is because the purchases price looks for all purchases to cal weighted avg, but ignores the fact that we could have sold or squared off our previous purchase and starting a fresh on the same stock
This tutorial is absolutely amazing. Thanks so much for sharing!
Liked and subscribed!
This Portfolio Tracker is amazing, but the issue I facing that when I put the Dividend & DRIP under any Security Symbol with Quantity on the History tab is also increased my Quantity of shares on the Positions tab also... For that, it's affecting the Purchase...
Can you please elaborate that how can I put the Dividend & DRIP of any Security under the History tab?
Thank You!!
I'm wondering the same thing for Dividend. For DRIP - you need to enter the fractional share amount that was purchased, and your share count will be correct, but for non-DRIP Dividends, it's altering the share count when it shouldn't. Does it need to be labeled as "Cash" instead of a ticker because it's like a different type of deposit?
@@adamwentworth I am having the same issue. I thought I may have been entering the DRIP wrong. I wonder if there is a code/formula fix for this or if it's just a matter of logging it into History differently.
I think I may have figured this out. If you add +sumif(History!C:C,"Dividend",History!F:F)-sumif(History!C:C,"DRIP",History!F:F) to the end of the formula in the cell for Cash, and then make sure you enter dividend payments and DRIP payments separately in the History, it should add the dividend payment to cash and then subtract it while still correctly changing your share amount on the Positions tab. I THINK it also correctly updates the dollar cost average amount in the Purchases tab as well, but I have not tested that yet.
And I just realized you have to put 0 for the Quantity in the History when entering Dividends. Otherwise it adds an extra share on your Positions tab.
Arrr... nevermind. That's not working either. It messes up the totals in History. I'll keep working on it.
Amazing! Just finished making this. It was so much fun and it looks great! Thank you!
dividend always adds up on the positions. if I sell positions in history and there's a dividend that thing remains as shares in my positions tab
Did you find a solution here?
Same here, I'm trying to find a solution. When you check the video at 36:47, you can see how he registered the DRIP in history, Idk how he calculated the quantity. I tried to do something different using dividend yield and price but it didn't work :(
Did you find a solution?
Put 0 in the amount of stocks
Hi, thank you for sharing your wonderful portfolio tracker. I am still working my way through it. Very comprehensive instruction.
Question: For the calculation Average Purchase Price in the Position sheet, it doesn't seem to take into consideration if a specific stocks are partially sold.
Example:
1 jan 2021 MSFT Buy 200 $100
1 feb 2021 MSFT Sell -200 $120
then
1 mar 2021 MSFT Buy 300 $130
The Average purchase price should only take the purchase on 1 Mar and ignore the purchase on 1 Jan since it has already been sold in Feb.
but it seems the formula still takes both Jan and Mar into the averaging.
Not sure if I am correct.
youre such a g dude. these are priceless.
This is brilliant! Very well put together and explained. I loved it! Thanks so much!
Very comprehensive tutorial, great job. Still I am facing the following issue: Let's say I had a stock that I completely sold at some point resulting in a realized gain. then, at a later stage I bought it again - the price now will be averaged with the old stocks, which I don't want to. How did you solved this one?
+1 for this issue. Looking for solution
+1 here as well
This help a lot. Thank you so much for sharing. Your effort of doing this video for more than an hour is super appreciated.
I liked, subscribed and didn’t skip ads to give back the favor. ❤️
Thank you for the support!!
Really Helpful, thanks a miL! How would one go about deducting a Fee off the units and allowing this to be shown NET in the Share column?
Your spreadsheets are totally awesome! Thank you for sharing!
This was an excellent and very helpful video!
However, after creating the tracker for my portfolio, I found it was rendering inaccurate results.
I dug into it and found that the method to calculate purchase price (starts at around 22:26) will lead to an incorrect value for anyone who has sold the stock and again bought it for a different price.
For example, someone buys a stock at $100 and then sells it for $150. Now, again he is buying the same stock at $120. In this instance, his average purchase price calculated by the given method will be an average of $100 and $120 = $110, but his actual average purchase price should be $120.
Can anyone suggest a way to correct this?
You can add a check for cell C to only contain Buy orders by adding History!C:C="BUY" to the formula like this:
=IF(ISBLANK(A3),"",AVERAGE.WEIGHTED(FILTER(History!E:E,History!B:B=A3,History!D:D>0,History!C:C="BUY"),FILTER(History!D:D,History!B:B=A3,History!D:D>0,History!C:C="BUY")))
This goes in the purchase category, Column C in the Positions tab :)
Has anyone figured out how to fix this?
@@alejandrovillalba8163 the formula i posted above fixes this issue :)
@@pibbz13 is it possible that the comment is hidden? I cannot see it. Would you be so kind to copy paste it under my comment, please?
I suggest disregarding anything this fool posts because I just wasted a lot of time and figured out what you just said also
Excellent knowledge bro, I Made my portfolio tracker too with the help of this video. Thankyou.
Thank you. Is there any way to track options ( calls/Puts) as well?
You make the best videos I learned so much using google sheets than I ever have
Glad to hear that!
issue I am having is when making the chart on breakdown sheet. When highlighting the value from A3 to A100 it doesn't allow me to click ok. It shows Invalid Type.
Just two days ago I was watching you other video about sheet tracker. Very helpful content , thanks a lot 🙏
Thanks for watching, glad you like my content!
hello, i wrote the script exactly as explained but when i go to tickers i get "Error
Reference does not exist." Any idea how to solve it???
same here.. Instead I make my own functions hehe
Really appreciate your selfless effort, this is amazing!
First of all Great tutorial. Thanks A lot!
Only Question i have is:
How Do i make Cash deposits in the History tab not to appear in the Positions tab, it messes up the calculations on the Dashboard.
because what happens when i input deposits, it refers to it as a stock and counts it with all the other stocks in the Positions Tab
I too find CASH as positions in Position TAB - can't figure out where have I made an error in the Script.
@@JayuPatel Had the same problem, it seems it gets solved by tiping "Cash" only C in capitals. That worked for me
@@santiagolamberti2181 Thanks, that was a big help!
I have the same issue. I input Cash with only C capital but still it shows up in positions. Anyone had the same issue and solved it?
@@therecedinghairline look at your coding. How did you spell "Cash" in your script? "CASH" or "Cash" ?
Thank you, this is exactly what I was looking for!
Thanks for this video! Very helpful!
Is there any way to sort the Positions tab by Alphabetical Order of the stock ticker? I would love it if it updated automatically when buying or selling new positions.
Right before return total put total.sort() on the line before it.
@@mitanchandihok6343 Thank you, this works perfectly! Much appreciated!
Wow! Two (2) thumbs up to you for bringing this video to the masses. I hung in there from start to finish, learned a plenty and now I've got my own tracker. Much appreciate your efforts...Stay Safe!
That's a nice tracker!
Wow! no puedo creer que recien hoy descubro éste video! Amazing! !
Saludos desde Argentina.
Gracias!
I'm getting a #REF! error when I enter the function at 18:26. I have double checked my code for any errors, but it is identical to his. Can someone help with this?
Were you able to fix it? I'm having same problem
Me too, I'm searching for another video like that
I had the same error. Went through my code with a magnifying glass and found a typo. Now it works like a charm. So check the code again for typos!
Same problem
This is amazing work! Thank you so much for teaching me some new stuff today.
I get Error: "ReferenceError: values is not defined (line 16)." on my =MyPortfolio(History!B3:B1000;History!D3:D1000). Any suggestions? I check the Script Ln 16 = "sums[t] = Number(values[i])" am I missing something still?
I am getting same error msg:
Great video, how would options fit into this?
Loved the vid took me a while to get it right had to go to dc for the myportfolio but all worked great. Keep up the good work. Subbed and liked. Ps i would love to incorporate crypto any change you will look in to that?
Yes please!
Finally, I am done. Thanks a million for the tutorial in such great depth, explaining every single line of the code. Kudos to you man 👏🏼👍🙏🏼🙏🏼
Great video. One questions. When I include stocks from other countries/ exchanges, how do I incorporate currency conversion (i.e. foreign currency to USD - using live exchange rates) into the spreadsheet so that the prices get updated automatically? Thanks heaps.
Thanks! And on Friday I will release a video that shows you how to convert currencies!
Awesome video man, made both of my portfolio trackers from your videos - love the content!
This is a very in-depth sheet and i would love to use it just dont have time to build you. Do you charge to have one made? Thank you
Hey max, you can find all my trackers to download on my Patreon! The link is in the description!
@@ThinkStocks I would like to take a curse on this specific subject of coding
@@ThinkStocks how do I get to your patreon
The link is right in the description!
Great video. Just one more suggestion, everytime you buy and sell equity, we pay the exchange some charges, would suggest you to include a column for same, so that these charges can be accounted for.
does this work with options? also what other trackers you offer in your patreon?
Great tutorial. Just what I needed to create my own portfolio trackers for testing ideas and tracking my real-money portfolio.
Thanks!
Thank you so much for taking the time to walk us through this process. The code to copy and paste made creating the sheet super easy! You are a gentleman and a scholar!!!!!
After I did the coding and save it ... id tried to use it bust it says an error of non-existing function... :( any suggestions ?
Same, but check @Oliver Mark comment, he said to put it like this at 18:19 use a ; instead of a comma: =MyPortfolio(History!B3:B1000;History!D3:D1000); then it works
edit: and in the history total use the ; as well instead of the two commas
Excellent work, absolutely amazing.
i keep getting "#ref?" after i try my function even my code are identical with him, can anyone help?
Appreciate you for taking the time to explain your portfolio tracker. Very interesting DIY tool. 🙏🏼
Thank you!