Build Your Own Stock Portfolio Tracker On Google Sheets
HTML-код
- Опубликовано: 21 янв 2021
- Build Your Own Stock Portfolio Tracker On Google Sheets
📈 Link to download all my Portfolio Trackers: / thinkstocks
🌎 Follow me on Twitter: / keithfrislid
🎥 Here is the camera I use for my videos: amzn.to/2PB865Q
🔶Check out my new website if you are interested in achieving financial freedom and peak physical health!🔶
www.fitbodiesfatwallets.com/
In this video, I will break down how to make this stock portfolio tracker in google sheets. This video is long so make sure to get yourself a snack and get ready to crank this out over the next hour! This portfolio tracker is the most automated advanced tracker you will find on RUclips! We will not only be pulling live market data and using complex google sheets functions, but we will also be creating our very own custom function called =MyPortfolio().
Please do not be intimidated by the length of this video and the complexity of the functions. I will you through every single step so anyone can make this for themselves!
Link to a MAJOR code and Tracker update: • Google Sheets Master P...
RUclips Doesn't Allow greater than or less than signs in the description so I replaced them with these symbols: "▶","◀"
🔷Google Sheets Coding Tip:
🔸 99% of people will mess up on this code line: for(i = 0; i ◀tickers.length; i++){
🔸 Make sure this is free of any spelling mistakes, or syntax errors!
🔷Google Sheets Functions:
History Tab:
🔸 Total: =(if(isblank(B4),"",Abs(E4*D4)))
Positions Tab:
🔸MyPortfolio: =MyPortfolio(History!B3:B1001,History!D3:D1001)
🔸Purchase: =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)))
🔸Price: =if(ISBLANK(A3),"",GOOGLEFINANCE(A3,"Price"))
🔸Change%: =if(ISBLANK(A3),"",GOOGLEFINANCE(A3,"changepct")/100)
🔸Change$: =if(ISBLANK(A3),"",GOOGLEFINANCE(A3,"change")*B3)
🔸Cost: =if(ISBLANK(A3),"",C3*B3)
🔸Value: =if(ISBLANK(A3),"",D3*B3)
🔸Gain%: =if(isblank(A3),"",(D3-C3)/C3)
🔸Gain$: =if(ISBLANK(A3),"",H3-G3)
Dashboard Tab:
🔸Account Value: =B3+B4
🔸Positions: =SUM(Positions!H3:H30)
🔸Cash: =sumif(History!C:C,"Deposit",History!F:F)-sumif(History!C:C,"Withdrawal",History!F:F)+sumif(History!C:C,"Sell",History!F:F)-sumif(History!C:C,"Buy",History!F:F)
🔸Day change: =sum(Positions!F3:F30)
🔸Unrealized Gains: =sum(Positions!J3:J30)
🔸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)
🔸Number of Trades: =countif(History!C:C,"Buy")+countif(History!C:C,"Sell")
🔸Shares Owned: =sum(Positions!B3:B20)
🔸Average Share Cost: =(Sum(Positions!G3:G16))/B10
🔸Number of cash deposits: =countif(History!C:C,"Deposit")
🔸Money Hourly Salary: =(B6+B7)/2080
🔸Dividend Income: =sumif(History!C:C,"DRIP",History!F:F)
AFFILIATE DISCLOSURE: Some of the links on this channel and in video descriptions are affiliate links. At no additional cost to you, we receive a commission if a purchase is made after clicking the link.
#googlesheets #portfoliotracker #googlefinance
![Cordae - Saturday Mornings (feat. Lil Wayne) [Official Music Video]](http://i.ytimg.com/vi/z2GYJua09F0/mqdefault.jpg)
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
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.
This is truly helpful. I just finished my portfolio by adding all the stuff you taught. Awesome!
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.
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!
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, 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
This was an amazing tutorial, thank you so much for taking the time and sharing this!
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!
Wow, you even gave us the functions/formulas in the description. Great work bud.
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.
Thank you so very much for taking the time to put this together--YOU ROCK! Works like a charm! Great tutorial!!
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 want to press the like button more than once. Great stuff!
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 👏🏼👍🙏🏼🙏🏼
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🔥
Awesome spreadsheet man 🔥🔥🔥 I love creating my own views with Sheets or Websites to interpret the stock mkt data I care about.
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.
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!!!!!
Thank you so much for creating this! Best tutorial so far.
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 was so helpful!! I've been wanting to create something like this for myself to manage my multiple accounts. Thank you for you time!
loved everything, my portfolio improved a lot! Thanks for the whole content
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.
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 wish I could like it twice to thank you for it. I have been managing my portfolio with google finance but what you have done here is next level. I am also very interested in coding and visualisation so well done. This is trully amazing!
Thank you!
Thank you so much!! learned a lot and had a lot of fun building my own tracker!
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
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.
Fantastic video and tutorial. This is exactly what I needed. Thank you so much for explaining everything so well.
Really appreciate your selfless effort, this is amazing!
That's a nice tracker!
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.
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!!
Dude, you have one of the best content about google sheets / finance of RUclips. No kidding!!!! Subscribed.
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?
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!
Awesome video man, made both of my portfolio trackers from your videos - love the content!
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.
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!
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!
Amazing! Just finished making this. It was so much fun and it looks great! Thank you!
Thank you. Is there any way to track options ( calls/Puts) as well?
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.
Great tutorial. Just what I needed to create my own portfolio trackers for testing ideas and tracking my real-money portfolio.
Thanks!
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!
does this work with options? also what other trackers you offer in your patreon?
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.
Thanks for the video!
I did it! It took much more than in video, because of different issues, but I also added supporting of 2 different currencies into all sheets :)
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!!
Is there a way to do this with options as well?
Thank you for the great video,, when I add dividend how do I remove it from the portfolio position sheet?
Input as Cash
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!
this is the best portfolio i tried!. thank you for taking time to do this
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!
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!
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.
a huge Thank You for your vids! I made a custom sheet inspired by your walk thru vids. this dashboard was great to mod to show my various accounts in a CLEAN custom dashboard & learned so much, where to find data, how to imbed it.
Thanks! Glad you enjoyed my tutorial!
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
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!
Thanks and I just refractory my existing sheet, that’s totally an big evolvement
Today I found this video and it blew my mind. Damn, that was damn good. Thanks a lot for the time and effort you put into this. Really helpful.
youre such a g dude. these are priceless.
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
Great, can't wait to dive into this. Also I find the hourly wage thing such a fun and good metric to track my progress. Somehow it works as a great motivator and puts it into a very ground eye level sort of understanding of money working for you.
Glad you like the video and thats exactly my thoughts as well on it!
@@ThinkStocks Hey quick question on the hourly income. I was working on recreating this metric for my own dashboard. You base the hourly wage based on realized and unrealized gains. This doesn't factor in dividend income correct? I feel like the dividend income contributes to that stat. Can you explain why you don't factor it in? Is it because all your dividend income is repurchasing stock automatically? For me there are some dividend stocks that don't generate enough to drip yet (no fractional shares on my platform). Hence my question whether or not to include it.
@@overnightgrowth I have the same problem on dividend
The perfect portfolio tracker! Thanks for your hard work. Keep it up and I look forward to future videos.
Thank you!
@@ThinkStocks Only took me 5 hrs, but its almost complete. Still have to make some tweaks in the History tab to account for all the "weird" transactions.
Id say 5 hours is pretty good timing!
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!
Hey I'm looking for a template in excel that has all the major markets, sectors indrustries etc and market direction for year, qtr, month, week ,daily. Any direction would be helpful.
This is absolutely SUPERB!!!!!Thank you so much. This is way better than most of the apps out there
Much appreciated, thoroughly enjoyable, thank you!
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
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
DAM! This is the most complete portfolio sheet I have ever seen. Well done
You make the best videos I learned so much using google sheets than I ever have
Glad to hear that!
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???
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" ?
Incredible attention to detail on video and formulas in description. Thanks for this.
That was an amazing tutorial..thanks mahn!
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
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?
Awesome video! I made a few changes as my portfolio is more dividend focused, but it was easy since you taught so much in the video. Thanks!
How did you fix this issue? If i have dividends without DRIP its adding it in the positions section.
Which issue are you talking about?
love the video, took me a day or so to do, but totally worth it. You are a legend!
Glad you liked it and thank you!
i keep getting "#ref?" after i try my function even my code are identical with him, can anyone help?
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.
Loved the video. Learned so much in a short period of time. Appreciate the work.
This is brilliant! Very well put together and explained. I loved it! Thanks so much!
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
Great work man. You are much appreciated 🙏. I m looking forward to more videos.
Thank you!
@@ThinkStocks Can this be done in excel because sometimes google sheet doesn't load up formulas properly.
Thank you for another fantastic and highly practical video!
Glad you enjoyed it!
Thank you man. Super useful tutorial!
Can you pin on top of the comment section all the comment regarding the improvment of the code?
Cheers!
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
Man this is awesome!
Wow this is awesome! Thnx! 💪🏼
plase fix.
cannot read property 'length' of undefined
thanks.
If you are just clicking RUN on the script, you will get the error. Ignore it. He says that in the video
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:
Bro this is so clutch, You deserve a viral video, 100k subs here we come!
Thanks man!
Superb as usual, keep the good work going
Thanks man!
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