Investment Tracker in Google Sheets! (Track Stocks, Crypto, ETFs, and Dividends)

[@skylerjohnson1455]

This is possibly the most complete tracker ive seen on youtube. Thank you for sharing this!!

[@Dividendology]

You’re welcome!

[@Leon_Aldrich]

I second this, but I wonder if Dividendology has caught up on Game of Thrones, Wheel of Time and Witcher?

[@Dividendology]

@@Leon_Aldrich LOL! Great point!

[@zackgilbert3109]

this is one of the best trackers ive seen on YT

[@casafilms]

@15:12 I get an error with your formula: "Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive."

[@jordancannaday7351]

Im getting the same error

[@MMuniz]

Wow this is amazing. I’m Watching this while I work, but I will be coming back and making this for my portfolio this weekend and following along. I never knew you could do 80% of this stuff on google sheets. Very cool.

[@Dividendology]

Thank you! I’m glad you like it!

[@Daddywaah]

Sweet baby Jesus. I had no idea Google Sheets could do all this, much less someone has figured it all out in a stock tracker package. Decided to become a patron just on the sheer skill this guy has shown.

[@Dividendology]

Thanks for the kind words Jeff! I have lots of useful tools on my patreon page. I hope you enjoy!

[@Daddywaah]

@@Dividendology Yeah I'm looking at the Patreon page now. It's like Xmas in July! :D

[@ManabuMaxOrido]

Very nice job!! I have already followed your instructions and it works perfectly! Keep up with the good work!

[@Dividendology]

Thank you!

[@JuanMartinez-pn8jm]

@dividendtology the formula for the crypto price is not working. Can you please update the formula?

[@jeanroyer4167]

Thanks for your reply for Canadian stocks. Looking forward for the update. Your videos are great!!

[@FilipKramarski-q8n]

Hello im having trouble with the divident tab i copy pasted the formula and didnt work, i saw one comment saying to change the parameter 9 with 10 still didnt work what do i do

[@MrRlopez15]

I am having this same issue, I have tried watching your video tutorial and watching this video, editing the links and the values and still continues to give me error messages. Any fix idea?

[@Tesla-Cannon]

Is there a tab of where you keep track of when you buy/sell items? I'm buying the same funds every month for the same purchase price, so the price per share I buy at would be different every time. It looks like you're tracking the lump sum because none of those symbols are repeating in your chart, unless you're just adding a row each time? I'd be happy to buy, but I just want to make sure I understand the process. I saw you mention a "unique" formula in other comments, but I'm not great with formulas so I'm not sure what that means.

[@Dividendology]

Hi Tesla! The unique formula is a good way to track the same purchase at different times, but if you don’t feel comfortable using that, then one option you have is to edit the amount of shares you own as well as the purchase price. You would simply need to change the purchase price to what your average purchase price is. Let me know if you have more questions.

[@chele850]

You can add a “transactions” tab, then in the “stocks” tab use the sumif formula to total the shares owned for each ticker. There’s an average weighted formula that would work for the price per share.

[@tioopuh]

An update to this would be great, some companies don't report their holding the same way or it might just be me

[@Dividendology]

check out this video!: kzbin.info/www/bejne/Z3_ZgZdunrNriac

[@ПростойДоход]

Great video. There is no such video in my country and in my language)) Hello from Russia

[@remyheinis]

What about following Futures and Options, commodities, etc... in the dashboard ? No you only buy shares ?

[@kal9448]

Thank! What can I use for German stocks?

[@dividendseeker8406]

This is AWESOME!!

[@joaobarbosa3807]

Hi. Great video, very useful. However the importXML function appears to result in the input being text so I’m unable to perform any calculations on it. For example multiplying the price by the number of coins I have. Is there any way round this?

[@joaobarbosa3807]

Same happens in the dividend function. Any idea how to change it, because you just select the cell and everything is fine

[@solonasstavrou9146]

where I have A2 choose your own cell with the ticker inside

[@clearlyconfused9194]

Hi, Using the pre-built sheets, is there a place to log the purchase and sales of stocks, so I can keep track of buys and sales? Thanks

[@Dividendology]

This tracker currently only tracks your portfolio, so it doesn’t track sales. I’m planning on creating a tracker that can track sales.

[@mariomujica3339]

amazing job !!!! a lot thanks !!!!

[@Dividendology]

Thank you!

[@dunalar9454]

Great guide tbh one of the best I saw so far....quick question, how do you see progress over the period? If you want to see what growth you had 2018-2019-2020-2021 etc..? Secondly this comes with first question why don't you add date of purchase? I assume you will buy one stock few times so it will sum all various cost/average etc.

[@Dividendology]

Thanks! I appreciate the kind words. To answer your questions, it would be relatively easy to track growth over different time periods and to add purchase dates. Google finance has ways to automatically add the date when you enter a transaction, so that would help with the purchase dates. As for growth over different time periods, this would be a little more difficult to automate, but I'm sure I could find a way. I'll likely add that feature in the future.

[@coenkie7311]

when i try to do the dividend tab it says 'The value of Parameter 2 of function INDEX is 7. Valid values are 0 to 1.' how do i fix this

[@kylestewart4952]

Change the 9 in the new formula to a 10. Finviz added a line to the website.

[@HARIPRASATH82]

Unable to make payments from India for the patreon subscription. Kindly add crypto payment option.

[@ioanvladescu5987]

Great tutorial sir, thank you! Just one question regarding the formula for the current price of crypto assets. Although I've set the document to refresh recalculations every minute, it seems the price for coins does not refresh. Any idea why? Thank you.

[@Dividendology]

From my experience, google sheets will typically auto refresh this around every 20 minutes.

[@Tesla-Cannon]

If I also wanted to track my index funds, would I need to do the same thing as the ETFS? Also - sorry third question, I can't find all my ETFs on ishares (VYM, VTI, VGT, SPY) - Is there another place like that to find holdings by location?

[@Dividendology]

Yes, you would track the index funds the same way as ETFs. As far as pulling ETF/index fund data, you should be able to go to the website of the fund and find out what it’s holdings are. For example, you should probably go to Vanguards website to view the holdings within their funds.

[@TheInvestaBros]

Does it show your cost basis for a stock? Slet's say you DCA into a stock, does it show your cost basis?

[@Dividendology]

There are certainly multiple ways to do that!

[@lifeslittlelessons1]

I can't get the formula for the current price to work.

[@caueconomy]

Incredible tracker. But, if i bought different times the same asset? How can i add these events into the tracker ?

[@Dividendology]

There were multiple ways to do this. One way that I have used on some of my other trackers is using the =unique() formula. Using this formula will allow the asset you purchased to only show up once on your spreadsheets, and you can then take the weighted average of your buy or sell price. Other ways would be to add the purchase into your original purchase.

[@Dividendology]

I have used the =unique() formula often in my other portfolio tracker tutorials, especially when calculating things like “capital by industry”. You can refer to this if you need an example!

[@dunalar9454]

Hi, great video, but have one issue you didn’t mention. When you assuming buy another same stock for different price, the chart cannot handle it as it shows as duplicate record. Tried to google fix, but not able…would you have any suggestions? So same symbols it sums and creates an average purchase price for all stocks

[@Dividendology]

There is a way to do that! There is a formula in google sheets called =unique() This formula allows any value/Symbol to show up only once. If you were to use this formula and then a =sumif formula, you would be able to do exactly what you are saying. I go over these formulas in some of my videos and create some charts based off of these formulas.

[@tball-vk3uh]

the finviz formula didnt work..i copy and paste and input the cell where my stock and try table 7 or 8..still didnt work. Help please

[@Dividendology]

=substitute(SUBSTITUTE(index(importhtml("finviz.com/quote.ashx?t="&A23,"table",9),7,2),"*",""),"-","0.00") Try this.

[@MrRlopez15]

@@Dividendology Hello I am getting this same error, I used the new link and still getting the same error. My error says "Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive." Please help!

[@chrisc5991]

i have problems webscraping from fidelity...do i need to open an account there? Whenever I try to go the the console tab most of what it says is blocked by client

[@Dividendology]

They’ve changed their website so you can’t webscrape from there anymore… but I’ve developed new ways to webscrape the industry formula! I use it in my most recent dividend stock watchlist video.

[@chrisc5991]

@@Dividendology Thanks I'll look it up

[@chrisc5991]

@@Dividendology btw do you have a separate sheet for your transactions?

[@richiep6018]

Hi, thanks for the info. I'm trying to add the ticker GRT.UN, but it shows up as N/A, even though this is the correct ticker. Any idea how to fix this? All the other tickers properly load.

[@Dividendology]

Google finance might not allow it, but if you download the Tickerdata add on, it will allow you to track it. Tickerdata.com

[@ShyamaSonti]

@dividendology this a very nice tool to look at all investments in one place. I have few questions: 1) How do you recommend including 401k mutual funds in here - Could I add those fund tickers to the stock tab? 2) If I have multiple ETFs, would have to create one tab for each? 3) What is the last tab "Investment Data" for? Thank you.

[@chele850]

You can get the share price with: =GOOGLEFINANCE([ticker],""closeyest""). I haven’t figured out a way to get blend mutual funds to reflect correctly (ie VTWAX is US & International equity, 401k split between small and large cap funds for US {also reqs balancing the two}, etc.). If you’ve figured that out please share your wisdom! :)

[@bhavneshparikh8283]

Can be avialble Peronsal Finiance Planning Tracker in excel?

[@jimmygarcia1534]

How can I do monthly dividends?

[@upperhooddesign2332]

What if you buy multiple stocks on diferent prices in order to do dollar cost averaging?

[@Dividendology]

Just update your shares owned and list your average cost per share!

[@AlfiyaGHasuNP]

PLEASE GIVE US ANOTHER FORMULA FOR CRYPTO CURRENCY PRICE, THE ONE YOU PUT IN UR DISCTIPTION DOES NOT WORK. THANKS

[@Dividendology]

I just checked and the formula in the description seems to be working for me. Make sure you've done all the steps correctly, and that you have adjusted the formula in the video description to refer to where your crypto is listed!

[@jasonharding3928]

@@Dividendology It works for some coins but not for others? By chance where is the code getting the coin from? Would it be possible to have it have the abreviation of the coin rather then a specific name? For instance I cannot get it to list Shiba Inu. Bitcoin has to be spelt out and cannot use BTC.

[@jasonharding3928]

@@Dividendology This is only my 3rd excel / google sheet attempt at making a good tracking to try and keep track of things so I don't have too much experience and still trying to understand codes and scraping sites. sometimes get them to work others not so.

[@Dividendology]

@@jasonharding3928 the key is to list the crypto the way it is listed in its URL on Coinbase. For example, Shiba inu would be: shiba-inu

[@jasonharding3928]

@@Dividendology ok I will try thank you so much for the reply

[@olres77]

This portfolio Tracker is good but not perfect for multiple transactions : 1) Transaction tab for BUY / SELL / DEPOSIT / WITHDRAWAL / DIVIDEND.

[@financewithjohnstanton2866]

Awesome!!

[@tomfletcher1735]

This sooooo fucking good thank you so much man!!

[@Dividendology]

you're welcome!

[@peircestillson584]

Phenomenal

[@leedale3395]

The formulas for the stock industry and the current crypto prices don't work for me :(

[@Dividendology]

I use Tickerdata to auto pull in my data into spreadsheets now! Tickerdata.com

[@leedale3395]

@@Dividendology Thanks but you have to pay for that so its no good for me :(

[@vladpruna95]

The Industry formula is not working anymore, seems the website changed some things

[@Dividendology]

Gonna make an update!

[@naitik079]

There is an error with the crypto formula. It says "value parameter cannot be parsed to number". Ran through your web scraping video and crypto tracking video and followed exact same steps and data but still the same error.

[@Dividendology]

Did you make sure to adjust the formula to make it refer to where your data is located? This is a common mistake.

[@naitik079]

@@Dividendology Yes I did adjust the formula as per data. In fact my sheet has exactly the same data placed as is in your sheet.

[@Dividendology]

It could potentially have to do with the website being under maintenance. Try refreshing your spreadsheet and see if anything changes.

[@jeppemilverts]

Go to settings and change the countrystandard to US. Maybe do a refresh on the formular. When it works, you can just change it back to your original country ;-)

[@S2DAJ]

any luck finding the sloution for this error? i am having the same issue.

[@salvatorelafranca6873]

can you build custom trackers i paid your membership fee to try to DM you and don't see your contact info ?

[@Dividendology]

There is a place to private message me on my patreon page!

[@ziggyzach8152]

Formula parse error

[@ChillPixels1]

Tthank you for your videos could you please make a crypto Portfolio Growth Chart (hourly - daily - monthly and yearly ) with timestamps, i mean record all the price changes (history) that happen to the portfolio balance overtime thanks.

[@Daddywaah]

Btw, following the Crypto section in this tutorial in June, 2022 does not look pretty. I sincerely hope the prices go back up for you Div! 😧

[@Dividendology]

Haha true! Fortunately this was just example data. I didn’t actually Put that much into crypto at those prices.

[@Daddywaah]

@@Dividendology lol, thank God!

[@BT-iv9gb]

😂

[@Dividendology]

Dividend: It seems finviz has changed the way to pull dividends from their website. I have corrected the spreadsheet and it now works. Here is the updated formula: =substitute(SUBSTITUTE(index(importhtml("finviz.com/quote.ashx?t="&A23,"table",9),7,2),"*",""),"-","0.00") Industry: =if(isblank(A23),"",index(importxml("eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols="&A23&"","//div[@class='sub-heading']//span[@class='right']"),1))

[@xenixnation8454]

This code doesn’t seem to work also

[@Tesla-Cannon]

You may want to pin this so it shows at the top. Left a comment about the formula not working, but this did the trick. :)