How to Retrieve Historical Stock Prices?

I have used VBA a few times and I am taking a finance class where I need to
be able to calculate a porfolio's risk (standard deviation).

Here is what I am trying to do:
With user defined stock symbols and date range, go to either MSN Money or
Yahoo!Finance and retrieve historical closing prices and populate it back in
Excel. From there do a little data manipulation to prep it for the
calculations. The goal is to make it user friendly, hence the use of VBA
forms, etc.

Here's my problem:
The data manipulation and calculations are not a problem once the data is in
Excel. My question is "How do I get the historical stock prices based on the
user defined data (symbol and date range) from MSN Money or Yahoo!Finance and
populate it in Excel?"

I have racked my brain for the past four days trying to figure it out. I
would appreciate any help.

Robert

goto the yahoo group called xltraders

http://groups.yahoo.com/group/xltraders/

I have a free file there called "GetYahooMultipleHistory" under the author
donalb36.
Just enter the dates desired and the symbols and frequency and enjoy.

--
Don Guillett
SalesAid Software
[email protected]
"Robert K" <Robert [email protected]> wrote in message
news:[email protected]...
>I have used VBA a few times and I am taking a finance class where I need to
> be able to calculate a porfolio's risk (standard deviation).
>
> Here is what I am trying to do:
> With user defined stock symbols and date range, go to either MSN Money or
> Yahoo!Finance and retrieve historical closing prices and populate it back
> in
> Excel. From there do a little data manipulation to prep it for the
> calculations. The goal is to make it user friendly, hence the use of VBA
> forms, etc.
>
> Here's my problem:
> The data manipulation and calculations are not a problem once the data is
> in
> Excel. My question is "How do I get the historical stock prices based on
> the
> user defined data (symbol and date range) from MSN Money or Yahoo!Finance
> and
> populate it in Excel?"
>
> I have racked my brain for the past four days trying to figure it out. I
> would appreciate any help.
>
> Robert
>
>

"Don Guillett" wrote:
> goto the yahoo group called xltraders
> http://groups.yahoo.com/group/xltraders/
> I have a free file there called "GetYahooMultipleHistory"
> under the author donalb36.

First, how do you find these tools? When I click on "Files",
I get an alphabetized list. Is that list complete? For example,
I do not find GetYahooMultipleHistory per se, but I do find
GetYahooMultipleHistory97a_P. Is that what you really
meant, or am I missing something?

Second, it would be nice if the xltraders contributors adopted
an open-source approach to their tools. For example, the
macros in your tool are passworded. Thus: (a) I cannot
learn from them; (b) I cannot leverage them -- i.e, modify
them for my own purposes or incorporate them into my
workbook; and (c) I cannot review the implementation to
be sure that it does not contain trojan horses, viruses, and
other malicious code.

(If the OP simply needs the data to cut-and-paste into the
OP's workbook, the OP might as well use Yahoo's CSV-formatted
download feature. But the OP's requirement seems quite
clear: the OP wants to implement a user-friendly interface
in __his/her__ own spreadsheet.)

Finally, what assurance does a user have that xltraders files
do not contain trojan horses, viruses or other malicious code?
I presume the answer is "none". In that case, I would only
open such spreadsheets (with active macros) in a limited-user
account. And if I cannot look at the macros, I would be loathe
to ever use the spreadsheet in an unlimited-user (adminsitrator)
account. That further limits the usefulness of xltraders tools.
Am I being prudent or too risk-adverse?

Perhaps this should be discussed in the xltraders group. But
I think it is appropriate to highlight the "red flags" here, since
you have steered a number of people to xltraders tools in the
past.

"Robert K" wrote:
> With user defined stock symbols and date range, go to either
> MSN Money or Yahoo!Finance and retrieve historical closing
> prices and populate it back in Excel.

Caveat emptor ....

Yahoo's "adjusted close" price includes an "adjustments" for
dividends. For example, see HPQ for 9-Dec-05 and 12-Dec-05.
If that satisfies your needs for your analysis of the portfolio
performance, great!

On the other hand, Yahoo's "close" price is not adjusted for
splits and other exchanges due to organizational changes (e.g,
spinoffs). And the "split" information that you see online is
not included in the CSV-formatted download data :-(.
Consequently, it is difficult to automatically compute the price
adjusted for splits and other exchanges based on Yahoo data.

From my file that you cited in your tirade. Yahoo CSV info on the Data page.
Date Open High Low Close Volume Adj. Close*
12/12/2005 30.00 30.12 29.72 29.97
10,127,700.00 29.97
12/9/2005 29.22 30.00 29.22 29.92
9,133,900.00 29.84


--
Don Guillett
SalesAid Software
[email protected]
"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> "Robert K" wrote:
>> With user defined stock symbols and date range, go to either
>> MSN Money or Yahoo!Finance and retrieve historical closing
>> prices and populate it back in Excel.
>
> Caveat emptor ....
>
> Yahoo's "adjusted close" price includes an "adjustments" for
> dividends. For example, see HPQ for 9-Dec-05 and 12-Dec-05.
> If that satisfies your needs for your analysis of the portfolio
> performance, great!
>
> On the other hand, Yahoo's "close" price is not adjusted for
> splits and other exchanges due to organizational changes (e.g,
> spinoffs). And the "split" information that you see online is
> not included in the CSV-formatted download data :-(.
> Consequently, it is difficult to automatically compute the price
> adjusted for splits and other exchanges based on Yahoo data.

Congratulations! You were able to find the correct file.. Were you able to
figure out how to use it?

I have no idea if the list is complete ?? as it is a list of files posted by
members who, if desired, have a right to their intellectual property. You
can always add sheets and use the data as desired. I just custom designed a
program for a user of this file. If you do not want to use files from this
group, don't.. If you want to discuss, join and rant all you like.

--
Don Guillett
SalesAid Software
[email protected]
"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> "Don Guillett" wrote:
>> goto the yahoo group called xltraders
>> http://groups.yahoo.com/group/xltraders/
>> I have a free file there called "GetYahooMultipleHistory"
>> under the author donalb36.
>
> First, how do you find these tools? When I click on "Files",
> I get an alphabetized list. Is that list complete? For example,
> I do not find GetYahooMultipleHistory per se, but I do find
> GetYahooMultipleHistory97a_P. Is that what you really
> meant, or am I missing something?
>
> Second, it would be nice if the xltraders contributors adopted
> an open-source approach to their tools. For example, the
> macros in your tool are passworded. Thus: (a) I cannot
> learn from them; (b) I cannot leverage them -- i.e, modify
> them for my own purposes or incorporate them into my
> workbook; and (c) I cannot review the implementation to
> be sure that it does not contain trojan horses, viruses, and
> other malicious code.
>
> (If the OP simply needs the data to cut-and-paste into the
> OP's workbook, the OP might as well use Yahoo's CSV-formatted
> download feature. But the OP's requirement seems quite
> clear: the OP wants to implement a user-friendly interface
> in __his/her__ own spreadsheet.)
>
> Finally, what assurance does a user have that xltraders files
> do not contain trojan horses, viruses or other malicious code?
> I presume the answer is "none". In that case, I would only
> open such spreadsheets (with active macros) in a limited-user
> account. And if I cannot look at the macros, I would be loathe
> to ever use the spreadsheet in an unlimited-user (adminsitrator)
> account. That further limits the usefulness of xltraders tools.
> Am I being prudent or too risk-adverse?
>
> Perhaps this should be discussed in the xltraders group. But
> I think it is appropriate to highlight the "red flags" here, since
> you have steered a number of people to xltraders tools in the
> past.

"Don Guillett" wrote:
> From my file that you cited in your tirade.

It was intended as a heads-up to the casual reader, not
a "tirade".

> Yahoo CSV info on the Data page.
> Date [...] Close [...] Adj. Close*
> 12/12/2005 [...] 29.97 [...] 29.97
> 12/9/2005 [...] 29.92 [...] 29.84

Demonstrating my point. There should be no difference
between close and adjusted close on those dates, if you
expect adjustments to reflect only splits and similar
exchanges due to spinoffs etc. Yahoo's adjustment on
12/9/2005 and earlier (until the next event) is the $0.08
dividend on 12/12/2005. You would see this if you looked
at the Yahoo historical quotes online.

I know that some people include dividends when
determining cumulative "total returns". I did not think
analysts include dividends in adjusted prices when
determining "average return" and especially "volatility".

Thanks for your help and insight!


"[email protected]" wrote:

> "Don Guillett" wrote:
> > From my file that you cited in your tirade.
>
> It was intended as a heads-up to the casual reader, not
> a "tirade".
>
> > Yahoo CSV info on the Data page.
> > Date [...] Close [...] Adj. Close*
> > 12/12/2005 [...] 29.97 [...] 29.97
> > 12/9/2005 [...] 29.92 [...] 29.84
>
> Demonstrating my point. There should be no difference
> between close and adjusted close on those dates, if you
> expect adjustments to reflect only splits and similar
> exchanges due to spinoffs etc. Yahoo's adjustment on
> 12/9/2005 and earlier (until the next event) is the $0.08
> dividend on 12/12/2005. You would see this if you looked
> at the Yahoo historical quotes online.
>
> I know that some people include dividends when
> determining cumulative "total returns". I did not think
> analysts include dividends in adjusted prices when
> determining "average return" and especially "volatility".

You may be interested in this spreadsheet which automates the data retrieval from Yahoo and calculation of the historical volatility http://investexcel.net/1979/calculat...atility-excel/