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.

 

[Simulationconsultant]

Try this spreadsheet - it downloads multiple stock quotes from Yahoo

 

[hajir]

I have a post on my blog explaining how to call the free Yahoo! Finance API to get Stock Data. The following code takes the data from Yahoo! Finance and displays it in the Excel cells. Feel free to look at the sample result on my blog.

Option Explicit

' Example: Requesting Stock info of GOOG, FB and MSFT.
'
' In this example, we want to obtain the following Stock info
'   - Name of company
'   - Last trade date
'   - Last trade (price only)
'   - Dividend Yield
'   - P/E Ratio
'
' Range A1 to be taken the head cell.
' Type "GOOG in range A2, "FB" in A3 and "MSFT" in range A4.
' Type "n" in range B1, "d1" in range C1, "l1" in D1,
' "y" in E1 and "r" in F1.
'
' We use the following URL for getting Stock Quote from Yahoo!
' URL: http://finance.yahoo.com/d/quotes.csv?s=FB+MSFT&f=snd1l1yr
'
' The CSV file data looks like the following
'
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' "MSFT","Microsoft Corpora","9/4/2013",31.24,2.89,12.36
'
' The columns in CSV file is separated by comma. Split function
' is used to split the data on each line of CSV file at every
' occurrence of comma. However, some commas in the CSV file are
' part of a string, not separating the columns. For example
' consider the following line,
' "FB","Facebook, Inc.","9/4/2013",41.7665,N/A,189.46
' The second comma is not a column separator. But it's part
' of the company's name "Facebook, Inc."
' This program will identify which comma is a column separator
' and which is part of a string

' ************
' Written by http://www.msexcelvancouver.com
' Thanks to http://www.youtube.com/watch?v=iSlBE3CWg5Q

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

    Dim head As Range
    Set head = Range("A1")

    Dim i As Integer
    Dim Symbols As String: Symbols = ""
    Dim SpecialTags As String: SpecialTags = ""
    Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://finance.yahoo.com/d/quotes.csv?s="
    Dim rng As Range
    Dim cell As Range

    ' Get the Stock Symbols
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
        Symbols = Symbols & cell.Value & "+"
    Next cell
    Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'

    ' Get the Special Tags
    Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
    For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
        SpecialTags = SpecialTags & cell.Value
    Next cell

    Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags

    Call Print_CSV(Yahoo_Finance_URL, head)

End Sub

' Download the CSV file and Print it in the cells
Sub Print_CSV(URL As String, head As Range)

    ' Get the CSV file from Yahoo! Finance
    Dim Http As New winhttprequest
    Http.Open "GET", URL, False
    Http.send

    Dim Resp As String: Resp = Http.responsetext
    Dim Lines As Variant: Lines = Split(Resp, vbNewLine)
    Dim sLine As Variant
    Dim Values As Variant
    Dim Value As Variant

    Dim i As Integer: i = 1
    Dim next_column As Boolean: next_column = True
    Dim col As Integer
    Dim tmp As String

    ' Extract CSV file Data to Cells
    For Each sLine In Lines
        Values = Split(sLine, ",")
        col = 1
        tmp = ""
        For Each Value In Values
            ' Read the info for this program which explains the lines below
            tmp = tmp & Value
            If Left(Value, 1) = """" And Right(Value, 1) <> """" Then next_column = False
            If Not next_column And Right(Value, 1) = """" Then next_column = True
            If next_column Then
                head.Offset(i, col).Value = Replace(tmp, Chr(34), "") ' Remove '"'
                tmp = ""
                col = col + 1
            End If
        Next Value
        i = i + 1
    Next sLine
End Sub