Apr
23

## Download historical stock price

A friend of mine asks me how to

1, using Excel 2003/2002 Add-in to download from MSN Money Stock Quotes. This add-in for Microsoft Office Excel 2003 and Microsoft Excel 2002 allows you to

http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

2, using the following macro to download from Yahoo finance. where you have to input start, end date and stock symbol

In Matlab there is build-in function named "fetch" for requesting data from Yahoo! data servers.

Hot posts:

15 Incredibly Stupid Ways People Made Their Millions

Online stock practice

Ino.com: Don't Join Marketclub until You Read This MarketClub Reviews

World Changing Mathematical Discoveries

Value at Risk xls

Random posts:

Vanna Volga Method

Numerical Integration Code

Option Pricing Models and Volatility Using Excel-VBA

Feedforward neural networks package

GEV distribution and density function

**download stock historical price**automatically with Excel, here are two ways I have played:1, using Excel 2003/2002 Add-in to download from MSN Money Stock Quotes. This add-in for Microsoft Office Excel 2003 and Microsoft Excel 2002 allows you to

**get dynamic stock quotes**from the MSN Money Web site. The add-in allows you to easily gather and study the stocks of interest to you.http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

2, using the following macro to download from Yahoo finance. where you have to input start, end date and stock symbol

Sub GetData()

Dim DataSheet As Worksheet

Dim EndDate As Date

Dim StartDate As Date

Dim Symbol As String

Dim qurl As String

Dim nQuery As Name

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("B1").Value

EndDate = DataSheet.Range("B2").Value

Symbol = DataSheet.Range("B3").Value

Range("C7").CurrentRegion.ClearContents

qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol

qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _

"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _

Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _

Symbol & "&x=.csv"

Range("b5") = qurl

QueryQuote:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))

.BackgroundQuery = True

.TablesOnlyFromHTML = False

.Refresh BackgroundQuery:=False

.SaveData = True

End With

Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=True, Space:=False, other:=False

Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"

Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"

Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"

Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"

End Sub

Dim DataSheet As Worksheet

Dim EndDate As Date

Dim StartDate As Date

Dim Symbol As String

Dim qurl As String

Dim nQuery As Name

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("B1").Value

EndDate = DataSheet.Range("B2").Value

Symbol = DataSheet.Range("B3").Value

Range("C7").CurrentRegion.ClearContents

qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol

qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _

"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _

Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _

Symbol & "&x=.csv"

Range("b5") = qurl

QueryQuote:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))

.BackgroundQuery = True

.TablesOnlyFromHTML = False

.Refresh BackgroundQuery:=False

.SaveData = True

End With

Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=True, Space:=False, other:=False

Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"

Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"

Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"

Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"

End Sub

In Matlab there is build-in function named "fetch" for requesting data from Yahoo! data servers.

**People viewing this post also viewed:**

Hot posts:

Random posts:

thanks

Add the followning VBA code at the end of teh macro:

Range(Range("C8"), Range("I8").End(xlDown)).Sort Range("C7"), xlAscending

Many thanks.

Incidentally, I've started looking at Google Finance as a source of data as Yahoo is missing a fair bit of historical data on non-US stocks, ETF's, etc. So far, I haven't got the query to return the data properly - annoying.