Build Your Own Portfolio Dashboard Tracker in 20 Minutes! (Portfolio Tracker in Google Sheets)
ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- Download my Spreadsheets: tickerdata.com/
Get 50% off of Seeking Alpha Premium!
www.sahg6dtr.com/9D5QH2/R74QP/
In this video, I take you step by step on how to build an automated portfolio dashboard tracker. Let me know if you have any questions!
*Formulas Below
1 Yr Trend Line: =if(A5="","",sparkline(GOOGLEFINANCE(A5,"Price",today()-364,today())))
Here is the updated formula to pull dividends and industry:
Dividends:
=(SUBSTITUTE(index(importhtml("finviz.com/quote.ashx?t="&I2,"table",10),7,2),"*",""))
Industry:
=if(I2="","",IFERROR(substitute(Index(IMPORTHTML("finviz.com/quote.ashx?t="&I2,"Table",8),3,1),"*",""),"-"))
=if(I2="","",Left(Q2, Search(" |",Q2)))
*Be sure to adjust I2 to where your ticker is located
Be sure to adjust these cells to refer to where your stock are listed.
Follow me on Twitter: / dividendology
Join my Discord! / dividendology
I am not a Financial advisor or licensed professional. Nothing I say or produce on TH-cam, or anywhere else, should be considered as advice. All content is for educational purposes only. I am not responsible for any financial losses or gains. Invest and trade at your own risk.
![[Full Episode] MasterChef Junior Thailand มาสเตอร์เชฟ จูเนียร์ ประเทศไทย Season 3 Episode 3](http://i.ytimg.com/vi/A1XXYbV048k/mqdefault.jpg)
Dividend Formula:
=if(A5="","",substitute(SUBSTITUTE(index(importhtml("finviz.com/quote.ashx?t="&A5,"table",9),7,2),"*",""),"-","0.00"))
Industry Formula:
=if(A5="","",if(isblank(A5),"",index(importxml("eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols="&A5&"","//div[@class='sub-heading']//span[@class='right']"),1)))
1 Yr Trend Line:
=if(A5="","",sparkline(GOOGLEFINANCE(A5,"Price",today()-364,today())))
Thanks for the very informative video! Unfortunately the industry formula didn't work for me. Is it because Fidelity has changed something on their website?
Any update on the link for the industry URL not working?
The Dividend formula does not work for me. Did it 3 times carefully to be sure I did it correct. Checked it over and over.
Keep getting an REF error saying Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive. Please help
@@fleshandfantasyadultsextoy5472 change the 9 to a 10
For me the new Industry formula that you put in the description is giving me an error message. Does anyone know how to solve that?
Thank you so much. Found it is very valuable to get the ball rolling. I am trying to DIY my own tracking dashboard.
I can’t believe I found this. Dude you are the Goat 🐐
I’m glad you found out! More fun stuff like this coming out soon 😎
Thank you so much for another wonderful job. You are absolutly amazing. I learnd a lot from you understanding google sheets and I enjoyed your presentation for Portfolio Tracker. Thank you again for a great job.
You are so welcome!
I like this video so much. Need to find time to create such excel files by myself
Thank you so much I learned tons that I can use for more spread sheets I want to make, and now I have a cool Portfolio dashboard
Glad it was helpful!
Wow, thank you so much!! I am going to go chart crazy right now😂
Superstar - thanks for uploading this! 👌🏽
Really appreciate it.
great video!
Great video, I spent over 50 minutes reviewing it, because it is a little bit old, but I also created my own sheet! Great work and thank you for helping your audience build their dividend wealth.
Glad it was helpful!
Great video :)
hello, i use sharesight. What are the advantages of maaking your own exel?
Bonsoir, bon travail, merci beaucoup. Good evening, good job, thank you very much
Thank you :)
well done. excellent video.
Thank you very much!
Is it possible to include the announcement date, ex dividend date & dividend payment date in the Spreadsheet?
Awesome. Working on my portfolio 02/02/24!
Best of luck!
Hey, great work on these trackers! I built one of my own based on your ideas, but I have a question.
If I buy the same stock 2 or 3 times, does google sheets know these are basically the same stock (same ticker) or do I need to do like an average price / share for all my purchases and put them in one table entry?
Thanks and greetings from Germany!
Thats awesome! And you simply just need to list the stock once and update the average buy price as well as the total amount of shares owned.
Fabulous
Hello, How to create Portfolio PE vs Market PE in spread sheet
hi can you use this to get funds in as well, i have 365 but only see stocks to bring in
thanks in advance
How to inculcate automating rebalancing in it. Like I provide the entry date and exit date for stocks in my portfolio
in my dividend column import from finviz come like text and does not allows me to make subtraction, multiply,etc. Any recommendation how to tranform this column to a number? Thanks
Is it possible to track bonds as well via google finance?
Hi. My sheets says this: "Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive" for the dividend formula. Can you please tell me why?
I too received this error message today.
Important information to have on ur chart is PPS or BVPS ...
how do you put in that you sold a stock?
Top!!!
finviz has changed the dividend cell to a text value with both the div amount and % yield. where is a different source to get just the div amount as a number value?
Your portfolio tracker is awesome. I can download a copy by becoming a patreon member?
Yes!
Hello does this spreadsheet work with Canadian stocks ans does it calculate the average cost of a stock when you buy the same stock at different period in time? Thanks for the answer, waiting for your answer to join your patreon.
It does not currently work with Canadian stocks, but I’m currently looking for options for tracking Canadian stocks.
have the same interest for Canadian stock, need both us and Canadian.
@@Dividendology Yes, an excellent spreadsheet - Thanks for the tutorial. I am also interested in Canadian Stocks and retrieving the formulas like you do on Finviz.
would also like to track canadian stocks. awesome work
Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this.
KR
I am actually unable to fetch dividend from the given URL. The error shown is ' Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive. '
Same
where can one extract info for Dividend column for ETF's Canada?
Thank you so much for all your videos. Googlefinance is giving totally wrong price of REM (which is the ETF from iShares). For example now it’s giving 0.11 instead of 25.09. Maybe you could advice what might be the reason?
Interesting. You may need to indicate the exchange it trades on.
@@Dividendology Thanks a lot! You are right. Now it works and the price of stock is correct! But unfortunately the cell with dividend, doesn’t understand the symbol((
ah I see. I'll try and work on a solution for this.
Does each holding need to be listed once? I dollar cost average per paycheck
Each holding should be listed once, so list your average price.
YESSSSS
it would be nice if you can add functionalities for the ledger, e.g. buy MSFT multiple times and able to consolidate to a singe total sum.
Here you only show 1 stocks already consolidated..
You just need to list your average purchase price and total amount of shares owned!
what purchase meaning and how calculate
HI
How to get dividend, industry for international Market like Hong Kong in google sheets
Not sure if otherts have this issue but some of the formulas take very long to load. most of the times they stay on loading oe N/a. I dont think is the network speed but any ideas ?
I think that is Google. I've done similar things in the past and I've gotten error messages all over the place. It usually clears up pretty quickly.
Two questions. I have multiple positions with stocks, and I sell off lower positions as price rises. Is there a spreadsheet that shows profit, loss on all positions held? And since I scalp trade, I need updates quicker than every 15 minutes. Currently using a plug-in that updates every 10 seconds for excel. Love what you’re doing it would definitely be a patron if these issues have been addressed.
No answer
Thanks for the video, very helpful!
The formula for getting the amount of dividends does not work for me. I don't know why, but I keep getting a syntax error (
Let me look into this.
@@Dividendology I do not know how to do that. I reply with a link to the test spreadsheet on Google Disk - as a result, my comment is deleted.
The syntax for formulas depend on your language settings in google sheets, for example i have to replace "," with ";" or it will not work in my swedish language settings.
Nice video, ur dividend is based on ur cost at purchase. Most online platform does dividend based on market value.
What website would I use instead of finviz for my Canadian Stocks?
I’m working on a solution for Canadian stocks. Stay tuned!
How can we calculate the cost of stocks when selling some of them? This only works if we only buy, but selling and buying does not reflect the real cost
No answer?
Most of the tutorials just shows the holding not the buying, selling, deposits and withdrawals from the portfolio.
What if you bought shares at different prices, do you put in the average price? And the total amount of shares bought?
Yes. Total amount of shares and average price.
@@Dividendology An option would also be to create a UNIQUE list on the Worksheet and SUMIF on the Symbol. A chart of this can be copied to the main Dashboard. As long as the chart on the worksheet stays it will update the dashboard char as well...AND after watching some of your other videos, I see that you sometimes do that.
Trying to figure out what changed with the formula for dividends. It’s displaying a percentage instead of the dividend amount. For example, KO is showing “1.95 (3.28%)”. Just gotta get rid of that percentage part.
his formula is outdated, so now he's just trying to sell his spreadsheet for money rather than update it for his viewers
Hi, how to fix the error with changes on finviz now showing both dividend and dividend yield? Thanks.
Use the TickerData add on! You can get it at TickerData.com
What if purchase price has changed, would data still be accurate?
For example if you buy BAC one month at 37.31 first month, and next month you buy it again but this time at 34.0. How could we keep track of the actual market value when shares have different purchase prices over months?
Thanks
Just list your average purchase price.
@@Dividendology Thanks!
hey mate. what can we do if the dividend in finviz is wrong? As apple is giving 0.23 not 0.92
Multiply 0.23 x 4. We have 4 Quarters.
in the formula make sure that the link say "https" and not "http"
Some of my tickers don't show up using the Google Finance formula. Do you know why this would be? It seems to be a lot of .TO stocks (I am Canadian and buy a lot of stocks on the TSX). or .CN stocks. Is there a workaround for this?
Some international stocks don’t well right now, but I’m actually creating a custom solution to fix this!!
Industry field not working for me and my ticker symbol is in A5.
The dividend calculation on the holdings tab pulled from finviz in column K has stopped working. I have played around with the formula to try and correct this with no luck. Any ideas on how to repair this so this works as it did before?
I have the same problem
What if I have my stocks in 2 currencies, Euros and Dollars. How do I navigate this situation? Because Google Sheets just adds them up like they are the same, even though I did fill them in in euros and dollars. Do I just change my entire Google sheet to one currency, or can I try something else?
I have a video on how to create an international stock tracker. Check it out!
Great Video, just wondering do you have a video that teaches us to create a template for day trade? Many thanks.
Yes I do! Search “Dividendology google sheets day trade tracker”!
Hey for the industry stock I don’t know if it’s just me but the link no longer works
It's not just you😐
Hi, Could you share google sheet link , thanks
You can download it on Tickerdata.com !
What if you are getting dividends reinvested every quarter/month?
That is a shortcoming. Please expand the sheet to allow for dividend reinvestment and for additional purchases of the same or other stocks. This would be essential for the calculations of yoc and short vs long capital gains
I'm encountering the "Function INDEX parameter 2 value is 7. Valid values are between 0 and 2 inclusive." Error when trying to key in the dividend. How do I resolve it?
There are updated formulas for dividend. Check out updated stock watchlist videos I've put out to see them or you can join the patreon.
My google sheet says "wrong in formating" when i write almost every your code. What is wrong?
You may need to switch your Google sheets to the US version
Does this spreadsheet work with international stocks/etfs such as UK stock or a UK ETF?
Coming soon in an update
will this be announced by post or video?@@Dividendology
It says “resource at url not found” when trying to web scrape the industry name using the command you have in the description
Thanks for bringing this to my attention. Looks like fidelity changed their website. I've created a new way to pull industry into google sheets. I'll post a video soon.
@@Dividendology Awesome videos man, keep it up!
@@Dividendology Excellent, looking forward to it!
Does this work for Crypto as well?
I have videos on crypto trackers on my channel. Check them out!
Best u calculate dividend % ur self.
I'm trying to sign up for patreon, but it keeps rejecting any of my credit cards.....Any tricks I should know about? Thanks
I'm sorry to hear that! I would suggest reaching out to patreon customer support.
So you have to upload your portfolio to Google? I am not sure I want my financial data in Google's cloud or any cloud for that matter.
The industry webstrapper doesnt work for me,. says url could not be fetched gives a N/A
I've created a new industry web scraper. I'll make an update soon :)
I am having trouble with the industry formula. It comes up with an error: could not fetch url
I have a new method for pulling in industry into google sheets. You can see my method in my most recent dividend stock watchlist video!
@@Dividendology Which one would that be? I have watched most of them and can't find what you are referring to. What is the title of the video?
Dividend stock watchlist
Lets say i have 1 share of KO and a month later I buy another, how do I update the table?
You just list the total amount of shares you own and update your average purchase price!
@@Dividendology so if i bougth 1 share at 100 and the other at 150 I just assume 125 as the purchase value? does it bias the result in any way ?
hi I don't know why the formula for dividend doesn't works, any helps?
There is an updated formula for dividends! Ive updated all the sheets on my patreon.
Hello, when I use =googlefinance(A2,"changepct")/100 give a error, why? do you know? please help me, thanks
dont use the komma but use the >> ;
Could be the an issue with the ticker. What's the symbol?
and at the same time Google is tracking all your holdings for your profile
If you reach the port is ori . Why google exhaler doesn’t gave owner portrait to transfer.? Very easy to measure the price if they connect my own portfolio because was maximize account and then send 3 list to her bank for blocked user . And then sell without advisor . The stocker have some profit . actually say to us . We also need to use the profit . Top movers / dis . Can we send on first . But the stocker must go . Say it . I need help
They don’t need to do that my friend.
for the dividend formula i keep getting this error please help I don't know if Im entering it wrong or am i just stupid lol (Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive.\
Here is the new dividend formula!: =if(B59="","",substitute(SUBSTITUTE(index(importhtml("finviz.com/quote.ashx?t="&B59,"table",9),7,2),"*",""),"-","0.00"))
I changed the 9 in the formula to a 10 and it worked
Yep, new formula is 10.
okay, thank you. but pls control your talking speed for better understanding.
This is the same as excel correct?
Google sheets. Its slightly different, except everyone has free access to google sheets.
@@Dividendology would I be able to build your spread sheet in excel?
@@Dividendology Your videos have been very helpful and I'm considering becoming a patron member. I just need to figure out which spreadsheets would be most helpful in my investing journey.
The sheets are designed to only be used in google sheets. They operate much better that way, and it’s easier to access.
@@Dividendology Got it.
Why don't you just sell sheet?
You can download it on TickerData.com!
The industry is not working anymore as well
I have an updated formula for industry I use now.
@@Dividendology where can i find this? thank you:)
GoogleFinance() is a reliability disaster, unfortunately. You'll see a lot of #N/A returned.