💾 Purchase the files created in this video here: ryanoconnellfinance.com/product/excel-stock-tracker/ 👨💼 My Freelance Financial Modeling Services: ► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
the bullshit is if you have to use a Google Function from google sheets anyway in Excel, why not just use Google Sheets and make your life easier instead of making an easy process convoluted?
Great video for starting to create a useful dashboard! Question 1 (maybe a dumb one): How would I factor in something like purchasing more shares of stock(s) on different days at different prices? Like this shows the initial entry shares and their purchase price, but what if (using your table) I buy 5 more shares of Walmart for $101.00 on one day, then 10 more shares at $90.00 on another day, etc.? Does there need to another sheet with a table of purchases and then the average purchase price for that symbol? Question 2: What would be an appropriate/easy way to track when you sell shares of a stock and totally leave that stock? Like in the table above, what if we decided to sell all of the Walmart shares? How would you account for that profit or loss for records? Another table or a different way? Thanks for taking the time to answer!
Hey SC, I'm planning to make a more comprehensive dashboard in the future that will account for these situations. As with this dashboard, the best way to incorporate shares of the same ticker purchased at different prices would be to use a weighted average cost in the column that store cost per share. As for question 2, there isnt a very easy way to track this based on the current dashboard. You could start a new table and store the amounts of gains or losses their and then pull in those gains and losses into the main dashboard
@@RyanOConnellCFA Ryan - thanks for taking the time to reply. I appreciate the suggestions you gave and will see about modifying my version to take these into account. Use a weighted average for multiple buys of a ticker at different prices - got it! Will be on the lookout for more of your videos!
@@RyanOConnellCFA Hi Ryan, I have some observations aswell. I just finished creating my own dashboard following your excellent video, but I too noticed some observations which would be great to take into consideration: 1) The dashboard does not take into consideration stock splits. Which messes everything up. 2) The dashboard is not taking into consideration dividends reinvested and subtracted taxes. If these two things were figured out, it would be awesome. Thank you again for your videos.
Nice tutorial. My tip, stop using nested IF statements, use an IFS statement. Also, why not put the market cap as billions in the Google vs showing the full amount in Excel?
Sir, think if I've 10 stock portfolio in excel, it'll get update for every 20mins, if I want to check how much of return has given such portfolio from past 1 year. How can I check it and how can I compare with index?
HI this informative video very helpfull . i have just confusion , is there we can take acces from google finance directly to sheet ? i tried but later on formula enter by me which is no more workable , there is like #N/A ,When evaluating GOOGLEFINANCE, Google Sheets is not authorized to access data for exchange: '' Later on i started adding other name in the list , that time this error showed on the screen . your help highly appriciated . thank you
I think you may be able to follow this process for Mac, please let me know if this works for you: Web Query: You can create a web query in Excel for Mac, although the process is a bit more complex. Go to Data > Get External Data > Run Saved Query Then in the 'Choose a File' dialog box, switch the file type to 'All Files' Paste the URL of the web page containing the data into the 'File Name' box Excel will download the data and open the 'New Web Query' dialog box In this dialog box, you can choose which tables of data from the webpage you want to import
Awesome vid Ryan! Really thanks! I'll see more videos from your channel! You gain my sub and like! One question, the allocation shouldn't be better linked with # of shares? So it would be a fixed percentage number in my wallet. If i want to change my percentage allocation i have to buy or sell shares.
Thank you! Number of shares would be much less telling for allocation than the weights of the total value you have in each share. For example, lets say you had 1 share of stock ABC which costs $100, and you had 10 shares of stock XYZ which cost $1 each. If you only looked at number of shares, it would appear that you are 10x more allocated to XYZ, where as in reality your portfolio is actually 10 times more allocated to ABC
Hello! The reason is that we need to pull the data from Google Sheets if you do not have Microsoft 365. So the first 3 minutes of the video, we are setting up the retrieval from Google sheets, then after that we start a blank workbook in Excel
The googlefinance formula is great, but is there a way to specify which market you're wanting. I want to create this for companies on the ASX and NZX, but it seems that the default is the NYSE. Any ideas would be very helpful :-) Love your content!
Hey there, glad to here you like my videos! Yes, the Google Finance formula in Google Sheets does allow for specifying different stock markets, including those outside the United States like the ASX and NZX. To access data from these exchanges, you'll need to include the appropriate market prefix in the ticker symbol. For instance, to retrieve data for a company listed on the ASX, you would use the prefix 'ASX:' followed by the company's ticker symbol. Similarly, for a company on the NZX, you would use 'NZX:' followed by the symbol. Here's an example for a hypothetical company with the ticker symbol 'XYZ' on the ASX: =GOOGLEFINANCE("ASX:XYZ")
I am using Excel on a Mac (version 16.81) and I don't have the same option to "Get Data" from the Web. I have a "Get Data (Power Query)" option but none of the sub-options allow me to get data from the Web. Maybe I am missing something or maybe it's different on a Mac? Thanks for any help!
In Excel for Mac version 16.81, you might not have the direct "Get Data from the Web" option. Instead, use "Get Data (Power Query)" and then choose "From Other Sources" to find the option for web-based data import. If this option isn't available, you may need to update Excel or consider alternative methods like using Google Sheets for web data retrieval and then importing it into Excel.
ok thanks - I couldn't find an option that would let me do it under the other sources tab either. I might be stuck using Google Sheets :(. Excel is so much better! @@RyanOConnellCFA
On my get data option when I click from web it opens a web browser with a script error popup, when I paste url in it just downloads the google sheet. using excel 2016 plus
Hello Ryan, this is not an error that I've ever heard. If I were you, I'd try googling the exact error message to find solutions others with the same problem have had
Hey Kevin, thank you! That is definitely possible. You will need to adapt the logic used in this video to do it: ua-cam.com/video/jPziXoUHZuo/v-deo.html
Really appreciate it Jakai! You can expect a lot more just like it. I don't think you could factor in moving averages with this dashboard as you would need historical data
Great Video, I have a question how would you incorporate the dividend factor in this dashboard so that we can get proper insight into total return (also XIRR besides absolute gain)
Hello! Thank you for your kind words and for raising an interesting question. I may make a video on this topic in the future. Incorporating dividends into your stock dashboard can be a valuable addition, providing a more comprehensive view of total return. Here are some steps you can take to include dividend data in your dashboard: Dividend Data: You can use a data source like Yahoo Finance, FRED, or Alpha Vantage to gather historical dividend data for your stocks. For example, in Google Sheets, you can use the GOOGLEFINANCE function to fetch dividend data by using the "DIVYIELD" attribute: =GOOGLEFINANCE("TICKER", "DIVYIELD") Replace "TICKER" with the actual stock ticker symbol. Dividend Reinvestment: To account for dividend reinvestment, you can use the DRIP (Dividend Reinvestment Plan) method. Calculate the number of additional shares purchased at each dividend payout date using the dividend amount and stock price on that date. Keep track of the total number of shares held over time. Total Return: Incorporate the dividend-adjusted number of shares into your percentage return calculations. For daily returns, compare the current total value (including reinvested dividends) to the previous day's total value. For the return since purchase, compare the current total value to the initial investment amount. XIRR Calculation: To calculate the XIRR (Extended Internal Rate of Return), you'll need to record each cash flow, including the initial investment, dividend payouts, and the current value of the investment. Use the XIRR function in Google Sheets or Excel to calculate the annualized return: =XIRR(cashflow_values, cashflow_dates) Replace "cashflow_values" and "cashflow_dates" with the appropriate cell ranges in your spreadsheet. By incorporating dividend data and adjusting your calculations accordingly, your dashboard will provide a more accurate representation of total return and XIRR, accounting for both capital gains and dividend income. Happy investing!
@@RyanOConnellCFA Thank you very much for your insight, I'll try to incorporate dividend and XIRR in my dashboard, I hope to see more content from you, cheers mate!
@@shivamtehri7633 Please let me know how it goes! I want to make a more comprehensive dashboard in the future but I don't think most people want to watch an hour long video lol
I do not think that the in-built Google Finance formulas in Google sheets can support these technical analysis indicators. However, I think you could sign up for a free AlphaVantage account and use their API to pull in these indicators
Could you use time stamps as to when they were gainers vs losers in the video? I could have shot the video over multiple days as well which may cause some confusion for you
Hello Sourabh, I think that you likely meant to comment this on my Excel portfolio optimization video! But to answer your question, I'm not sure how you could adapt the portfolio optimization methods used in finance to measure food quality
Great video--going to do this for my school's finance club, since we have a google sheet already. More videos about importing data/company info from the web to excel would be great! To that point, do you have any recommendations for sourcing historical data for running valuations and/or getting stock prices? I have used yahoo finance in the past but I hate their website (ads), and I have recently discovered how to download excel files from the SEC. Thanks!
Hey Tyler, thank you! Please come back let me know how the session goes with your Finance club. I have been thinking a lot about sourcing stock data lately as a freelance client of mine is me automate the calculation of portfolio performance for a bunch of portfolios. I'll likely make a video on all the stock data sources in the future. I really like Google Sheets, but the problem is that there is no way to get adjusted close stock prices (historical prices that adjust for dividends, stock splits, etc). I also like the Excel Microsoft 365 STOCKHISTORY() function but it has the same problem as Google Sheets, no adjusted close prices. Ultimately, I think I'll use Python to pull in directly from Yahoo Finance API (no need to go to their website at all that way). The client is intimidated by programming but I think I can get him on board to let me do this outside of Excel
@@RyanOConnellCFA Yes I will, and I will give you a shout out at the club meetings--I'm at Penn State and we currently have a small fund ~$35k with mostly ETFs and a few blue chips. But that is interesting you lean towards Python, I have seen some videos on that and have considered looking into it further. Now that you mentioned it I will have too. Thanks for the insight!
@@tylermilsop 35K is a big portfolio for a college finance club! Really appreciate the shoutout Tyler. Definitely checkout Python! I will be making a video on how it can be used for Finance very soon
💾 Purchase the files created in this video here: ryanoconnellfinance.com/product/excel-stock-tracker/
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
Zero bullishit. Excellent explaination.
Thank you!
the bullshit is if you have to use a Google Function from google sheets anyway in Excel, why not just use Google Sheets and make your life easier instead of making an easy process convoluted?
You get a like on that F4 trick... I've been looking for something like this for months... Appreciate your content...
Thank you for the feedback and good luck building your dashboard!
Excellent job... It helped me a lot. Now I need to find the way to track my options portfolio...
Glad it was helpful! Let me know if you find a way to track your options portfolio. I can also look into this topic in the future
Easy and well explained. Thanks man. 👍👍
Great video for starting to create a useful dashboard!
Question 1 (maybe a dumb one): How would I factor in something like purchasing more shares of stock(s) on different days at different prices? Like this shows the initial entry shares and their purchase price, but what if (using your table) I buy 5 more shares of Walmart for $101.00 on one day, then 10 more shares at $90.00 on another day, etc.? Does there need to another sheet with a table of purchases and then the average purchase price for that symbol?
Question 2: What would be an appropriate/easy way to track when you sell shares of a stock and totally leave that stock? Like in the table above, what if we decided to sell all of the Walmart shares? How would you account for that profit or loss for records? Another table or a different way?
Thanks for taking the time to answer!
Hey SC, I'm planning to make a more comprehensive dashboard in the future that will account for these situations. As with this dashboard, the best way to incorporate shares of the same ticker purchased at different prices would be to use a weighted average cost in the column that store cost per share.
As for question 2, there isnt a very easy way to track this based on the current dashboard. You could start a new table and store the amounts of gains or losses their and then pull in those gains and losses into the main dashboard
@@RyanOConnellCFA Ryan - thanks for taking the time to reply. I appreciate the suggestions you gave and will see about modifying my version to take these into account.
Use a weighted average for multiple buys of a ticker at different prices - got it!
Will be on the lookout for more of your videos!
Awesome, thanks for the reply! I will definitely look to make a more comprehensive one down the road
@@RyanOConnellCFA Hi Ryan, I have some observations aswell. I just finished creating my own dashboard following your excellent video, but I too noticed some observations which would be great to take into consideration:
1) The dashboard does not take into consideration stock splits. Which messes everything up.
2) The dashboard is not taking into consideration dividends reinvested and subtracted taxes.
If these two things were figured out, it would be awesome. Thank you again for your videos.
Learned a lot. Really helpful. Thanks!
Thanks for the feedback!
Nice tutorial. My tip, stop using nested IF statements, use an IFS statement. Also, why not put the market cap as billions in the Google vs showing the full amount in Excel?
Stumbled across fantastic Ss. Trying to create my own on a Macbook and unable to enter change in Attribute. What could be the issue?
Sir, think if I've 10 stock portfolio in excel, it'll get update for every 20mins, if I want to check how much of return has given such portfolio from past 1 year. How can I check it and how can I compare with index?
HI
this informative video very helpfull . i have just confusion , is there we can take acces from google finance directly to sheet ? i tried but later on formula enter by me which is no more workable , there is like #N/A ,When evaluating GOOGLEFINANCE, Google Sheets is not authorized to access data for exchange: '' Later on i started adding other name in the list , that time this error showed on the screen .
your help highly appriciated .
thank you
the "from web" option to get data doesn't seem to exist on my MAC addition of Excel. any ideas?
I think you may be able to follow this process for Mac, please let me know if this works for you:
Web Query: You can create a web query in Excel for Mac, although the process is a bit more complex.
Go to Data > Get External Data > Run Saved Query
Then in the 'Choose a File' dialog box, switch the file type to 'All Files'
Paste the URL of the web page containing the data into the 'File Name' box
Excel will download the data and open the 'New Web Query' dialog box
In this dialog box, you can choose which tables of data from the webpage you want to import
Does this work with coin Geico for cryptocurrency and also if we wanted to add a option for market cap change is that possible?
Awesome vid Ryan! Really thanks! I'll see more videos from your channel! You gain my sub and like!
One question, the allocation shouldn't be better linked with # of shares? So it would be a fixed percentage number in my wallet.
If i want to change my percentage allocation i have to buy or sell shares.
Thank you! Number of shares would be much less telling for allocation than the weights of the total value you have in each share. For example, lets say you had 1 share of stock ABC which costs $100, and you had 10 shares of stock XYZ which cost $1 each. If you only looked at number of shares, it would appear that you are 10x more allocated to XYZ, where as in reality your portfolio is actually 10 times more allocated to ABC
Hi, How do I add new stock symbol to the dash board?
How can you pull in the Forward Dividend Yield. If possible, is there a way to pull in Morningstar Rating, plus Analyst Rating?
Hello, sorry I don't think this info is available using Google sheets API
im doing the portfolio following your steps, why did you actualy started on total blank sheet after 3min ? Whats the point :) ?
Hello! The reason is that we need to pull the data from Google Sheets if you do not have Microsoft 365. So the first 3 minutes of the video, we are setting up the retrieval from Google sheets, then after that we start a blank workbook in Excel
@@RyanOConnellCFA thank u
@@jkstudioart My pleasure
Great job!
Thanks John!
The googlefinance formula is great, but is there a way to specify which market you're wanting. I want to create this for companies on the ASX and NZX, but it seems that the default is the NYSE. Any ideas would be very helpful :-) Love your content!
Hey there, glad to here you like my videos! Yes, the Google Finance formula in Google Sheets does allow for specifying different stock markets, including those outside the United States like the ASX and NZX. To access data from these exchanges, you'll need to include the appropriate market prefix in the ticker symbol.
For instance, to retrieve data for a company listed on the ASX, you would use the prefix 'ASX:' followed by the company's ticker symbol. Similarly, for a company on the NZX, you would use 'NZX:' followed by the symbol. Here's an example for a hypothetical company with the ticker symbol 'XYZ' on the ASX:
=GOOGLEFINANCE("ASX:XYZ")
nearly 10K subs, bro
Almost there Randy!
This is a good video, but I have a question it is not showing the price of some stocks
Thank you! It is possible that those tickers are not supported by Google Finance. What is an example of a stock that isnt working?
why make this when there is google finance already? Is there smth of added value
It is just for people that prefer Excel over Google sheets which I believe most people do
I am using Excel on a Mac (version 16.81) and I don't have the same option to "Get Data" from the Web. I have a "Get Data (Power Query)" option but none of the sub-options allow me to get data from the Web. Maybe I am missing something or maybe it's different on a Mac? Thanks for any help!
In Excel for Mac version 16.81, you might not have the direct "Get Data from the Web" option. Instead, use "Get Data (Power Query)" and then choose "From Other Sources" to find the option for web-based data import. If this option isn't available, you may need to update Excel or consider alternative methods like using Google Sheets for web data retrieval and then importing it into Excel.
ok thanks - I couldn't find an option that would let me do it under the other sources tab either. I might be stuck using Google Sheets :(. Excel is so much better! @@RyanOConnellCFA
did you ever figure out the solution to this? i’m struggling with the same thing.
@@BrendanClark-ri6iv I did not. I ended up setting up a Google Sheet instead.
@@BrendanClark-ri6iv I'm having the same problem. Have you found a solution?
On my get data option when I click from web it opens a web browser with a script error popup, when I paste url in it just downloads the google sheet. using excel 2016 plus
Hello Ryan, this is not an error that I've ever heard. If I were you, I'd try googling the exact error message to find solutions others with the same problem have had
Hi Ryan, excellent video. Thank you.
Question , can I adapt the purchased version for use on the Toronto Stock Exchange. Cheers Kevin H
Hey Kevin, thank you! That is definitely possible. You will need to adapt the logic used in this video to do it: ua-cam.com/video/jPziXoUHZuo/v-deo.html
can i know is there a key to extract freefloat marketcap
Hello, not that I'm aware of
Super Like 👍
Thank you!
Thanks for sharing. However still can't use because still need Microsoft Excel. 😢
Is this the time and value weight method?
Wonderful content, is there a way to factor in moving averages ?
Really appreciate it Jakai! You can expect a lot more just like it. I don't think you could factor in moving averages with this dashboard as you would need historical data
Thanks. Can we use this for international stocks
Hello, it can be used for international stocks but you need to specify a different exchange
Great Video, I have a question how would you incorporate the dividend factor in this dashboard so that we can get proper insight into total return (also XIRR besides absolute gain)
Hello! Thank you for your kind words and for raising an interesting question. I may make a video on this topic in the future. Incorporating dividends into your stock dashboard can be a valuable addition, providing a more comprehensive view of total return. Here are some steps you can take to include dividend data in your dashboard:
Dividend Data: You can use a data source like Yahoo Finance, FRED, or Alpha Vantage to gather historical dividend data for your stocks. For example, in Google Sheets, you can use the GOOGLEFINANCE function to fetch dividend data by using the "DIVYIELD" attribute:
=GOOGLEFINANCE("TICKER", "DIVYIELD")
Replace "TICKER" with the actual stock ticker symbol.
Dividend Reinvestment: To account for dividend reinvestment, you can use the DRIP (Dividend Reinvestment Plan) method. Calculate the number of additional shares purchased at each dividend payout date using the dividend amount and stock price on that date. Keep track of the total number of shares held over time.
Total Return: Incorporate the dividend-adjusted number of shares into your percentage return calculations. For daily returns, compare the current total value (including reinvested dividends) to the previous day's total value. For the return since purchase, compare the current total value to the initial investment amount.
XIRR Calculation: To calculate the XIRR (Extended Internal Rate of Return), you'll need to record each cash flow, including the initial investment, dividend payouts, and the current value of the investment. Use the XIRR function in Google Sheets or Excel to calculate the annualized return:
=XIRR(cashflow_values, cashflow_dates)
Replace "cashflow_values" and "cashflow_dates" with the appropriate cell ranges in your spreadsheet.
By incorporating dividend data and adjusting your calculations accordingly, your dashboard will provide a more accurate representation of total return and XIRR, accounting for both capital gains and dividend income. Happy investing!
@@RyanOConnellCFA Thank you very much for your insight, I'll try to incorporate dividend and XIRR in my dashboard, I hope to see more content from you, cheers mate!
@@shivamtehri7633 Please let me know how it goes! I want to make a more comprehensive dashboard in the future but I don't think most people want to watch an hour long video lol
@@RyanOConnellCFA is this video already out? i would love to see it :) and I don't mind if it is an hour or 2 long.
thank you
@@Vitor108 It is already out and it only ended up being 30 minutes! ua-cam.com/video/6MlzMZ-B2ao/v-deo.html
Is there a way to import live intraday technical analysis data for indicators such as BOLL and RSI?
I do not think that the in-built Google Finance formulas in Google sheets can support these technical analysis indicators. However, I think you could sign up for a free AlphaVantage account and use their API to pull in these indicators
Any idea how to find SPY ETF?
You can just type SPY in there in the ticker column!
The AMBA and WMT are the gainers in this example. Why are they showing 'Red' in 1 Day Return S/%?
Could you use time stamps as to when they were gainers vs losers in the video? I could have shot the video over multiple days as well which may cause some confusion for you
@@RyanOConnellCFA Awesome! I watched it again. Looks like, it's all good. Thanks.
Sir can we implement this method(mean variance portfolio optimization) to evaluate quality of food
Hello Sourabh, I think that you likely meant to comment this on my Excel portfolio optimization video! But to answer your question, I'm not sure how you could adapt the portfolio optimization methods used in finance to measure food quality
Does this work to track cryptocurrency as well?
It's a portofolio tracker whihc means that we can use it for any asset
Great video--going to do this for my school's finance club, since we have a google sheet already. More videos about importing data/company info from the web to excel would be great! To that point, do you have any recommendations for sourcing historical data for running valuations and/or getting stock prices? I have used yahoo finance in the past but I hate their website (ads), and I have recently discovered how to download excel files from the SEC. Thanks!
Hey Tyler, thank you! Please come back let me know how the session goes with your Finance club. I have been thinking a lot about sourcing stock data lately as a freelance client of mine is me automate the calculation of portfolio performance for a bunch of portfolios. I'll likely make a video on all the stock data sources in the future.
I really like Google Sheets, but the problem is that there is no way to get adjusted close stock prices (historical prices that adjust for dividends, stock splits, etc). I also like the Excel Microsoft 365 STOCKHISTORY() function but it has the same problem as Google Sheets, no adjusted close prices.
Ultimately, I think I'll use Python to pull in directly from Yahoo Finance API (no need to go to their website at all that way). The client is intimidated by programming but I think I can get him on board to let me do this outside of Excel
@@RyanOConnellCFA Yes I will, and I will give you a shout out at the club meetings--I'm at Penn State and we currently have a small fund ~$35k with mostly ETFs and a few blue chips.
But that is interesting you lean towards Python, I have seen some videos on that and have considered looking into it further. Now that you mentioned it I will have too. Thanks for the insight!
@@tylermilsop 35K is a big portfolio for a college finance club! Really appreciate the shoutout Tyler. Definitely checkout Python! I will be making a video on how it can be used for Finance very soon
Imagine Mrs Watanabe getting a hold of this and rebalancing on a daily basis 😳
Lol, this is the first time I have heard this term and now you've got me going down a rabbit hole