Historical stock prices - Stock Valuations - Analysis of the Company, Investing in Stock Markets Video Lecture | Investing in Stock Markets - B Com

Hi, this is Yi from ExcelClout.com.
In this short video, I want to show you guys
a really cool Excel file I've put together
to download historical stock prices right
into Excel.
And with this nifty Excel file, you can download
historical stock prices for companies listed
in U.S. stock exchanges in less than a minute.
Without further ado, let's get started.
This is the link where you can download the
file, or you can get there by Googling Excel
Clout (C-L-O-U-T) historical stock prices.
And it should be the first link in the search
result.
Let's click on download and open the file.
The first time you open this file, you may
get this security warning because the file
was downloaded from the Internet.
Let's click on enable editing.
Also the file contains macros, we need to
enable the macros by clicking enable content.
And make sure you do this because it's the
macros that's going to automatically download
your historical stock prices.
This Excel file basically comes with a form
as you see here.
And downloading historical stock prices is
as easy as filling out this form.
Over here, you have a stock symbols reference
in case you need look up any stock symbols.
Let me show you how easy it is to download
your historical stock prices with this tool,
so you can focus more on your historical analysis
instead of chasing data.
First, we need to specify the stock symbols.
For example, I'm going to change the stock
symbols over here to AA for Alcoa, GS for
Goldman Sachs, and WMT for Walmart.
So just enter the stock symbols in a list
like this.
Next let's set the From date, say, all the
way back to 3/1/2004, and the To date to 3/1/2014,
so that the spreadsheet will collect 10 years
worth of historical stock data.
And last, you have the option to set the frequency
of your data.
If you set the frequency to daily, you're
going to get the last observed stock prices
at the end of each trading day.
And weekly would give you last observed prices
at the end of each week.
So basically you'll get the last observed
prices at the end of the selected period.
You have other options such as monthly, quarterly,
and yearly.
Normally we'd keep the frequency at daily
so we get all the historical stock prices
for every trading day.
So now we're ready to click this big Download
Data to New Sheet button, which is the heart
of the this tool.
When you click this button, what's going to
happen is the specified historical stock price
data will be automatically be downloaded and
organized into a new sheet within this file.
If you look over here, you see two sheets
in this file right now: the Control and Sheet1.
As soon as I click this download button, a
new sheet with the collected historical stock
data will be added.
So let's click on the download button and
let the macros get to work.
And you see that a new sheet called Sheet2
has been added.
Let's go to Sheet2 and check out stock data
we just downloaded.
We have the dates in one column and the historical
prices for each stock in separate columns.
Now if you look at these stock prices, you
see lots of decimals.
That's because these are adjusted closing
prices which account for historical stock
splits and dividends.
The stock data is downloaded from Quandl.com
(Q-U-A-N-D-L).
Quandl is sort of like the Wikipedia for financial
data.
And thanks to Quandl's unlimited access, you
can download as much data and as frequently
as you need.
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 helpful.
And you can find all of them on my free tools
page on my website as well as on my Youtube
channel.
I really like to know what you guys think
about this tool, and if you have ideas for
other Excel tools, feel free to leave me a
comment.
And if this tool is able to help you save
some time or work, please give this video
a thumbs up and subscribe.
See you guys next time!