Build Your Own Stock Portfolio Tracker On Google Sheets
ฝัง
- เผยแพร่เมื่อ 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 TH-cam! 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...
TH-cam 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
![[HIGHLIGHT] หมีLIVEปะ? | EP.171 เห็นแต่ในจอ วันนี้มาต้องขอคารวะ](http://i.ytimg.com/vi/gPWLxa0e_Z8/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.
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.
This is truly helpful. I just finished my portfolio by adding all the stuff you taught. Awesome!
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!
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!
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.
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 👏🏼👍🙏🏼🙏🏼
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?
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.
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.
Thank you so much for creating this! Best tutorial so far.
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!
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.
I want to press the like button more than once. Great stuff!
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🔥
Really appreciate your selfless effort, this is amazing!
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!!
Awesome spreadsheet man 🔥🔥🔥 I love creating my own views with Sheets or Websites to interpret the stock mkt data I care about.
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
Dude, you have one of the best content about google sheets / finance of TH-cam. No kidding!!!! Subscribed.
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!
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:___.
Them simply do a if (or sumif) statement looking for CAD and multiplying the value using GOOGLEFINANCE("CURRENCY:USDCAD").
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!!!!!
Amazing! Just finished making this. It was so much fun and it looks great! Thank you!
That's a nice tracker!
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?
Incredible attention to detail on video and formulas in description. Thanks for this.
loved everything, my portfolio improved a lot! Thanks for the whole content
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.
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?
Your spreadsheets are totally awesome! Thank you for sharing!
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.
Thank you. Is there any way to track options ( calls/Puts) as well?
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.
This is absolutely SUPERB!!!!!Thank you so much. This is way better than most of the apps out there
Awesome video man, made both of my portfolio trackers from your videos - love the content!
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.
Is there a way to do this with options as well?
DAM! This is the most complete portfolio sheet I have ever seen. Well done
Fantastic video and tutorial. This is exactly what I needed. Thank you so much for explaining everything so well.
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
youre such a g dude. these are priceless.
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!
Loved the video. Learned so much in a short period of time. Appreciate the work.
does this work with options? also what other trackers you offer in your patreon?
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.
This is brilliant! Very well put together and explained. I loved it! Thanks so much!
This is the video that I have been looking for a long time. Thanks man!
Thank you for the great video,, when I add dividend how do I remove it from the portfolio position sheet?
Input as Cash
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!
Thank you so much!! learned a lot and had a lot of fun building my own tracker!
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!
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
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!
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!!
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!
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 is the best portfolio i tried!. thank you for taking time to do this
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 :)
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
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 tutorial. Just what I needed to create my own portfolio trackers for testing ideas and tracking my real-money portfolio.
Thanks!
Thank you, this is exactly what I was looking for!
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!
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
This tutorial is absolutely amazing. Thanks so much for sharing!
Liked and subscribed!
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!
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
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.
Thanks and I just refractory my existing sheet, that’s totally an big evolvement
Very Useful for any Stock Traders, Thanks a lot to give me the video as a wonderful gift.
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?
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
Very nice portafolio tracker and really useful step by step explanation, thank you
You make the best videos I learned so much using google sheets than I ever have
Glad to hear that!
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" ?
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
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!
Appreciate you for taking the time to explain your portfolio tracker. Very interesting DIY tool. 🙏🏼
Thank you!
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.
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 very much for the video. It is beautiful explained !!
i keep getting "#ref?" after i try my function even my code are identical with him, can anyone help?
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:
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!
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.
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???
This is amazing work! Thank you so much for teaching me some new stuff today.
Excellent work, absolutely amazing.
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!
How do I stop cash deposits from loading into my positions sheet?
strings are case sensitive, make sure the script is written correctly, and that everything matches.
I have the same question. Can anyone help?
@@subbat8458 Me too
Much appreciated, thoroughly enjoyable, thank you!
Thanks for all the great info!
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