stock downloading from yahoo in excel using vba

[hinchah]

i know this may be lengthy, but i figured someone must have already done this.

rows 1 and 2 are used for my parameters.
row 1 being used for descriptions: Ticker Symbol, Start Date, End Date, and pulling in the close price and volume for each ticker symbol with the selected date range. row 2 being used for entering the parameter info.
row 2 yould read (as an example)

MSFT, 1/1/2002, 1/1/2003, close price, volume.

For each ticker in column A, add a separate wksheet named the ticker symbol and pull in the corresponding info.

So the final result for MSFT, would be an added wksheet named MSFT, with the colums headers being the close and volume, and the rows being the date parameters set in row 2.

all using yahoo finance.

thanks for the help.
i cant seem to find code that will let me link to yahoo?? any help is greatly appreciated.

 

[Andrew Poulsom]

Here is some code I came up with by recording my actions and creating a web query. I have added some variables, which you can set by looping around the ticker symbols in column A.

Sub Test()
    Dim MSFT As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim a, b, c, d, e, f
    Dim StrURL As String
    MSFT = "YHOO"
    StartDate = DateSerial(2002, 3, 10)
    EndDate = DateSerial(2003, 1, 5)
    a = Format(Month(StartDate) - 1, "00") '   Month minus 1
    b = Day(StartDate)
    c = Year(StartDate)
    d = Format(Month(EndDate) - 1, "00")
    e = Day(EndDate)
    f = Year(EndDate)
    StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
    StrURL = StrURL & "s=" & MSFT & "&a=" & a & "&b=" & b
    StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
    StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
       .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
    Columns("A:F").EntireColumn.AutoFit
End Sub

Try it out. Hope you are able to modify it for your purposes.

 

[hinchah]

andrew,

thanks for the code. a few questions...

how can i set the code to perform the web query, for not only MSFT, but all values in wksheet "Template" with data starting in row A2 and continuing for no specific set range down the column.

also, for each ticker - insert a wksheet, MSFT, and in the new sheet "MSFT", do what the code has already done by pulling in all the info from yahoo.

and how can i set the date range, as opposed to reworking the actual code.
for instance, have the ticker in column A, start date in column b, end date in column c.

when the wksheet is added, it will pull in all the data (close, high, etc.) using columnA (the ticker) for the specific time period set in columns B and C on wksheet "Template" for that particular ticker, in this case being MSFT.

i appreciate the help.

 

[Andrew Poulsom]

Can you give me some examples of the tickers (MSFT) because I don't know any? Better still, post a sample of your data either using Colo's utility below or in the body of the message with cell references.

 

[hinchah]

Col. A Col. B Col.C

Ticker Start Date End Date
MSFT 11/10/03 11/14/03
KOPN 11/11/03 11/15/03
AAA 11/12/03 11/16/03
AAI 11/13/03 11/17/03
AAP 11/14/03 11/18/03
AAR 12/7/02 11/19/03
ABB 12/8/02 11/20/03
ABC 12/9/02 11/21/03
ABG 12/10/02 11/22/03
ABI 12/11/02 11/23/03
ABK 12/12/02 11/24/03

i would like the code to always create a wksheet for each ticker, and have yahoo look up the info (close, high, low...etc) for the corresponding dates in column's B and C.

the name and amount of tickers is always going to change.
however, they will always be on wksheet "Template", in column A2:XX

XX being the unknown. could be 200 tickers, could be 10.

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Ticker As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim a, b, c, d, e, f
    Dim StrURL As String
    Set Sh = Worksheets("Template")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlDown).Row)
    For Each Cell In Rng
        Ticker = Cell.Value
        StartDate = Cell.Offset(0, 1).Value
        EndDate = Cell.Offset(0, 2).Value
        a = Format(Month(StartDate) - 1, "00") '   Month minus 1
        b = Day(StartDate)
        c = Year(StartDate)
        d = Format(Month(EndDate) - 1, "00")
        e = Day(EndDate)
        f = Year(EndDate)
        StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
        StrURL = StrURL & "s=" & Ticker & "&a=" & a & "&b=" & b
        StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
        StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"
        ActiveWorkbook.Worksheets.Add.Name = Ticker
        With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
           .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1))
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
        Columns("A:F").EntireColumn.AutoFit
    Next Cell
End Sub

 

[hinchah]

works perfect, but when i try to only download one stock as opposed to 10 or 20 or 30, i get an error in the code at this line...

ActiveWorkbook.Worksheets.Add.Name = Ticker

here is the error:

Run-time error '1004':
Application-defined or object-defined error.

any suggestions.

thanks in advance.

 

[Andrew Poulsom]

Change this line:

Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlDown).Row)

to:

Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)

 

[hinchah]

thanks for all the help.

works perfect.

 

[Dwight]

Hello,

I would like to use this code but I can't get it to work. I am running Windows XP and Office 2002.

I created a workbook with a sheet named Template and set up the columns as shown.

The macro sets up the first sheet but then I get the following:

System Error &H80070057 (-2147024809) The parameter is incorrect.

There is no data in the sheet.

I am not a VBA expert but I went to the Yahoo Website and checked the code's references and they look okay. I noticed the first array argument is (1,4) which seems out of pattern. But when I substituted (1,1) it did not help.

I would really like to have the capabiltiy this code offers. Where am I going wrong?

Thanks in advance for any help.