Create a Live Stock Portfolio Tracker in Excel | No Microsoft 365 Required

[@RyanOConnellCFA]

💾 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/

[@ervinsmith2677]

Hello, My wife purchased these files for me to help with getting me organized. i am having several issues using the document. Clearly i am doing something wrong but cant seem to figure out where i am going wrong. I have watched this video several times but has not helped. First and most frustrating issue is that when i am attempting to update the google data tab. It keeps resetting anything i input back to the example info. Second issue is that it does not seem to want to accept the formulas. I do have Microsoft 360 and have considered just scrapping this document all together and building my own using this info but id like to save the time if possible. any assistance is appreciated.

[@RyanOConnellCFA]

@@ervinsmith2677 Hey Ervin, thank you for purchasing the file! Please send me an email at [e-mail] I have a better solution for you using Office 365, I just need your email to send it over to you

[@dashark347]

You get a like on that F4 trick... I've been looking for something like this for months... Appreciate your content...

[@RyanOConnellCFA]

Thank you for the feedback and good luck building your dashboard!

[@traderobserver9109]

Zero bullishit. Excellent explaination.

[@RyanOConnellCFA]

Thank you!

[@Matt90541]

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?

[@TomSilver_42]

Great. Unfortunately it looks like format changed on Google side, as attributes like "change" or "marketcap" mentioned at begining like at 2:20 are now all lowercase and separated not by coma but semicolon (at least what my GSheet now wants and has in F1 help). Just hint for those who would wonder why is this not working as it's in video.

[@franciscoviteri1765]

Excellent job... It helped me a lot. Now I need to find the way to track my options portfolio...

[@RyanOConnellCFA]

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

[@leslieerdman5220]

I might take you up on that in the future. I've been wanting a LIVE tracker for a long time.

[@RyanOConnellCFA]

Awesome!

[@MichaeldeBoer-f8t]

Can you point me to a tool that will allow me to set alerts for when to buy or sell, based on my own preferences? Say, if I wanted to run my portfolio in such a way that if any stock returned 50% or more, I should be altered to cash that profit in, or to set top values at which I might sell each stock I hold, based on my own risk tolerance?

[@leslieerdman5220]

Very helpful. I disagree with comment I like the nested IF's Thanks for speaking so clearly

[@RyanOConnellCFA]

Glad it was helpful! Thank you

[@dimitrispallis]

Great work!!We thank you!How can i calculate the 52 week high?

[@CCAA2233]

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!

[@RyanOConnellCFA]

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

[@CCAA2233]

@@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]

Awesome, thanks for the reply! I will definitely look to make a more comprehensive one down the road

[@dava12333]

@@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.

[@ahmadyousef3059]

Many thanks, How can I track buy and sell of my stocks?

[@RyanOConnellCFA]

My pleasure! This dashboard allows for the tracking of individual stock purchases and sales: kzbin.info/www/bejne/bH7Pq4CQYqdll9E

[@ChetanSankannavar-cp5yn]

Hi, Thanks for the great video & knowledge sharing. i have a query that the do we have to keep Googlesheet open to get updates on MSExcel? for example if can i not just open Excel and refresh for stock updates without opening googlesheet from my drive? please answer and please make video if there is any other procedure or steps to be follwed thank you

[@finiteloops8610]

Learned a lot. Really helpful. Thanks!

[@RyanOConnellCFA]

Thanks for the feedback!

[@y66llhuaweu98]

Thanks. Can we use this for international stocks

[@RyanOConnellCFA]

Hello, it can be used for international stocks but you need to specify a different exchange

[@joelcalvary1087]

Thank you Ryan!

[@RyanOConnellCFA]

My pleasure!

[@jakaisherriff8060]

Wonderful content, is there a way to factor in moving averages ?

[@RyanOConnellCFA]

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

[@earldominiquepaqueo1868]

Easy and well explained. Thanks man. 👍👍

[@saarshalit521]

YOU CAN MAKE A VIDEO TO SHOW HOW YOU UPDATE IF U SELL OR BUY NEW STOCK

[@razyh0074]

Hi, great video! It was very fun to watch and I learned a lot. However, I don't know why, but the googlefinance commands for Price and Market Cap is not working... Can someone help me with this issue please? Thanks

[@deeturner3467]

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!

[@RyanOConnellCFA]

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")

[@mikehawkins9678]

The first and second column worked really well but the third column did not work at all on the price change because when I hit the Data tab in google docs it did not give me all those options.

[@anamikasinghbhadoria9939]

This is a good video, but I have a question it is not showing the price of some stocks

[@RyanOConnellCFA]

Thank you! It is possible that those tickers are not supported by Google Finance. What is an example of a stock that isnt working?

[@leslieerdman5220]

How long do u keep losers before getting rid of?

[@RyanOConnellCFA]

For me it completely depends on how I feel about the position going forward and what type of account the position is in (taxable vs tax-protected). If I think the price fall was too extreme and the stock is going to rebound, and the position is in a tax protected account, then I'll just wait it out. However, if it is in a taxable account that has other unrealized gains I want to realize on a different position, I may use the loser to offset the gains on the winner to avoid taxes

[@kevinh7985]

Hi Ryan, excellent video. Thank you. Question , can I adapt the purchased version for use on the Toronto Stock Exchange. Cheers Kevin H

[@RyanOConnellCFA]

Hey Kevin, thank you! That is definitely possible. You will need to adapt the logic used in this video to do it: kzbin.info/www/bejne/oIHdmouliq2Nq9E

[@KimIngerslev-i9e]

How can you pull in the Forward Dividend Yield. If possible, is there a way to pull in Morningstar Rating, plus Analyst Rating?

[@RyanOConnellCFA]

Hello, sorry I don't think this info is available using Google sheets API

[@diario_e-bike]

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.

[@RyanOConnellCFA]

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

[@StepUpE-tutors]

Is this the time and value weight method?

[@benhgt]

Stumbled across fantastic Ss. Trying to create my own on a Macbook and unable to enter change in Attribute. What could be the issue?

[@karimrajani2735]

Hi, How do I add new stock symbol to the dash board?

[@harishn5873]

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?

[@shivamtehri7633]

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)

[@RyanOConnellCFA]

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!

[@shivamtehri7633]

@@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!

[@RyanOConnellCFA]

@@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

[@Vitor108]

@@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

[@RyanOConnellCFA]

@@Vitor108 It is already out and it only ended up being 30 minutes! kzbin.info/www/bejne/bH7Pq4CQYqdll9E

[@bblife777]

Does this work with coin Geico for cryptocurrency and also if we wanted to add a option for market cap change is that possible?

[@tf9151]

Any idea how to find SPY ETF?

[@RyanOConnellCFA]

You can just type SPY in there in the ticker column!

[@chingo9914]

Thanks for sharing. However still can't use because still need Microsoft Excel. 😢

[@naveenpasupuleti2122]

can i know is there a key to extract freefloat marketcap

[@RyanOConnellCFA]

Hello, not that I'm aware of

[@YatinGramo]

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

[@jhudsonwilliams861]

the "from web" option to get data doesn't seem to exist on my MAC addition of Excel. any ideas?

[@RyanOConnellCFA]

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

[@jasonandnadiaerdmann3344]

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?

[@TinaSmith-c8v]

When I transfer the google sheet to excel. The formulas don't transfer too. Why is this?

[@randybrickson4290]

nearly 10K subs, bro

[@RyanOConnellCFA]

Almost there Randy!

[@MiaBanks]

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!

[@RyanOConnellCFA]

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.

[@MiaBanks]

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

[@BrendanClark-ri6iv]

did you ever figure out the solution to this? i’m struggling with the same thing.

[@MiaBanks]

@@BrendanClark-ri6iv I did not. I ended up setting up a Google Sheet instead.

[@metinpasaoglu]

@@BrendanClark-ri6iv I'm having the same problem. Have you found a solution?

[@pinkusxy]

Sir can we implement this method(mean variance portfolio optimization) to evaluate quality of food

[@RyanOConnellCFA]

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

[@johncarroll6910]

Great job!

[@RyanOConnellCFA]

Thanks John!

[@EElia-bj9tp]

The AMBA and WMT are the gainers in this example. Why are they showing 'Red' in 1 Day Return S/%?

[@RyanOConnellCFA]

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

[@EElia-bj9tp]

@@RyanOConnellCFA Awesome! I watched it again. Looks like, it's all good. Thanks.

[@nickjanssen166]

alot of my stocks it cant find

[@leutytunes]

Is there a way to import live intraday technical analysis data for indicators such as BOLL and RSI?

[@RyanOConnellCFA]

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

[@ivannikolic6844]

why make this when there is google finance already? Is there smth of added value

[@RyanOConnellCFA]

It is just for people that prefer Excel over Google sheets which I believe most people do

[@tylermilsop]

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!

[@RyanOConnellCFA]

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

[@tylermilsop]

@@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!

[@RyanOConnellCFA]

@@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

[@ryansomerville5679]

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

[@RyanOConnellCFA]

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

[@jkstudioart]

im doing the portfolio following your steps, why did you actualy started on total blank sheet after 3min ? Whats the point :) ?

[@RyanOConnellCFA]

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

[@jkstudioart]

@@RyanOConnellCFA thank u

[@RyanOConnellCFA]

@@jkstudioart My pleasure

[@Yiiyoo16]

Does this work to track cryptocurrency as well?

[@ihebbibani7122]

It's a portofolio tracker whihc means that we can use it for any asset

[@hgaber105]

Super Like 👍

[@RyanOConnellCFA]

Thank you!

[@pepelepew1227]

Imagine Mrs Watanabe getting a hold of this and rebalancing on a daily basis 😳

[@RyanOConnellCFA]

Lol, this is the first time I have heard this term and now you've got me going down a rabbit hole

[@mthanson22]

nice video but malinformation in the title - you're using SHEETS not EXCEL - you're data isn't free!

[@RyanOConnellCFA]

90% of this video is in Excel and all of the data shown is 100% free

[@nickjanssen166]

im having trouble right away trying to add AP.UN because on google finance its listed as an american stock and in us$. any solutions?