Get Stock Quotes in Excel - Automatically - YouTube

Hi, this is Yi from ExcelClout.com.

In this video, I want to share with you guys a really handy Excel file I created to get

stock quotes from Yahoo Finance.

Now first of all this is not a tutorial on how to get stock quotes from Yahoo finance.

The file that I'm about to show you comes with that functionality built in, so you can

start tracking your stock investments in Excel right away.

So let's get started.

This is the link where you can download that file, or you can get there by Googling Excel

Clout (C-L-O-U-T) stock quotes.

And it should be the first link in the search result.

Let's click on download and open the file.

Now, the first time you open this file, you may get this protected view warning because

the file was downloaded from the internet.

We'll need to click on enable editing.

And this file contains macros, so we need to enable that by clicking enable content.

So right away you see this portfolio template.

Now if you're someone who's looking for a really easy way to track your stock investments

in Excel without having to write any VBA code or even formulas.

This the file you're looking for.

And I'm going to show you how to use this file.

So the convention here is: the light yellow cells ,over here, are the cells where you

enter your stock symbols and quantities, and all the other cells and charts will update

automatically based on the information entered in these light yellow cells.

The pie chart shows you your allocation, and the bar chart shows you your profit and loss

for the day.

The table over here contains information such as the price, which is the quote from Yahoo

finance, the change in price since last closed, the percentage change, the profit and lost,

and the total value for each stock symbol you've entered.

So let's play around with this table.

Let's say you own Yahoo stocks instead of Google stocks, so I'm going to change the

GOOG, which is the stock symbol for Google, to YHOO, which is the stock symbol for Yahoo.

And you see right away the charts and numbers are automatically updated.

Now let's change the quantity 12 to 30.

And again, the charts and numbers are automatically updated.

If your portfolio contains fewer stocks, you can always delete any extra rows that you

don't need.

So let's get rid of these three rows by first selecting them.

Right click and delete.

If your portfolio has more stocks, you can always insert rows into this table.

So let's select any row that crosses through this table.

Right click and insert.

Now suppose you own 50 shares of Coca-Cola.

And the stock symbol for Coca-Cola is KO.

And you have 50 shares.

Now to refresh the information on this sheet, including stock quotes and price changes,

just click on the refresh button over here, and you've noticed the numbers have changed.

You can also turn on auto refresh by clicking this auto refresh every minute button, which

automatically refreshes the quotes every minute at the first second of each minute.

And of course you have the option to disable that feature by clicking the stop auto refresh

button.

So no need to code or even enter any formulas to get stock quotes and track your stock investments

in Excel.

Now if you're a more hands-on person and you'd like to put together your own stock analytics

dashboard, you may want to check out the example sheet.

So let's go to the example sheet.

The example sheet basically contains example uses and documentation for all custom-built

formulas that come with this file.

So over here, you see that there are seven custom-built formulas.

For example you have stock quote, which returns the delayed stock quote for a given stock

symbol from Yahoo finance.

Stock change, which returns the change in stock price since last closed.

You can pretty much figure out what each formula does based on its name.

You have other formulas like stock day's low, stock day's high, stock year low, stock year

high, and stock volume.

The table on the left basically uses all seven formulas.

To give you an idea, if you look at the first row, AMZN, the stock symbol for Amazon, under

the quote column, and if you pay attention to the formula bar, it says equal stock quote

B10.

And if you look the the next column, the change column, the formula bar has equal stock change

B10.

So you get the idea.

One last note: when you're building your own stock analytics, you can copy and paste these

three buttons - the refresh, the auto refresh every minute, and stop auto refresh buttons

- to your new sheet within this file, and they will work just fine.

So that's it!

I hope you guys find this Excel tool useful.

And again, this is where you can download that file.

I've also put together other free tools you may find useful.

And you can find them on my free tools page.

So I really like to know what you guys think about this tool, what you like or don't like

about it.

And if you have ideas for other Excel tools, feel free to leave me a comment.

Hope you guys enjoyed the video!