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

Ryan O'Connell, CFA, FRM shows how to create a live updating stock portfolio tracker (dashboard) that DOES NOT require an Office 365 subscription or a Microsoft 365 subscription!

👨‍💼 My Freelance Financial Modeling Services:

🎓 Tutor With Me: 1-On-1 Video Call Sessions Available

CHAPTERS:
0:00 - Introduction
0:21 - Create a new Google Sheet document
0:45 - Use the Google Finance function to retrieve stock prices
2:26 - Connect Microsoft Excel to Google Sheets
4:10 - Input stock portfolio information
5:30 - Calculate stock market capitalization size
7:30 - Format to distinguish between manual inputs and formulas
7:57 - Calculate amount paid and current portfolio value
9:48 - Calculate portfolio allocations
10:15 - Calculate gains and losses on portfolio
12:00 - Conditionally format gains and losses
12:59 - Format the stock portfolio dashboard (tracker)
13:40 - Create stock position allocation pie chart
15:10 - Create sector and market cap pie charts
20:07 - Conclusion

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

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

Alternative Titles:
Create a Stock Portfolio Tracker in Excel With Live Prices | No Microsoft 365 Required
Create a Stock Portfolio Dashboard in Excel With Live Prices | No Microsoft 365 Required
Pull Live Stock Prices Into Excel
Pull Live Stock Prices Into Excel Without Microsoft 365
Pull Live Stock Prices Into Excel Without Office 365

[RyanOConnellCFA]

👨‍💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs:

[dashark]

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

[traderobserver]

Zero bullishit. Excellent explaination.

[franciscoviteri]

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

[earldominiquepaqueo]

Easy and well explained. Thanks man. 👍👍

[CCAA]

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!

[razyh]

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

[finiteloops]

Learned a lot. Really helpful. Thanks!

[jasonandnadiaerdmann]

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?

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

[chingo]

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

[deeturner]

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!

[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?

[jakaisherriff]

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

[anamikasinghbhadoria]

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

[harishn]

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?

[kevinh]

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

[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

[karimrajani]

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

[KimIngerslev-ie]

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