Yahoo Finance to Excel Automation

[Liosis]

Hello All,

First and foremost, I have very little VBA Macro experience and have a couple items I've been trying to accomplish that I'm hoping someone can help me out with. I currently have an excel spreadsheet that I constructed that does all sorts of stock analysis perfectly once I get the historical stock information imported to the first tab ("Input" tab)...my problem is that importing the individual stock data into the spreadsheet is a manual process right now that I'd like to automate (it will save me so much time).

So here is my current process:

1. I go to Yahoo Finance and type in the stock I'm looking for...we'll use "CL" for this example
2. Once I'm at the CL summary page, I navigate to the "Historical Prices" page via the left side menu
3. Once I'm at the "Historical Prices" page I scroll down to the bottom of the table where it says "Download to Spreadsheet" and right-click it and choose "Save Linked File to Desktop"
(This is the webpage version of the excel prices download: http://ichart.finance.yahoo.com/tabl...=d&ignore=.csv )
4. I then open the saved excel file, select all of the information, and copy and paste into the "Input" tab of my spreadsheet at cell A1
5. At this point, the yahoo page is still open and I select "Dividends Only" from the "Historical Prices" page options, click on "Get Prices", and then repeat steps 3 and 4 for the new dividend table with the only exception being that the information is pasted into cell I1 of the "Input" tab on my spreadsheet
(This is the webpage version of the excel dividends download:http://ichart.finance.yahoo.com/tabl...=v&ignore=.csv )

Note that the webpage links above use the following conventions a=(startmonth), b=(startday), c=(startyear), d=(endmonth), e=(endday), f=(endyear). For my purposes I believe just using using zeros for a, b, c, d, e, and f is fine as it will just retrieve all of the history for that stock from inception to present (which is perfect for me). Also, the only difference between the Prices URL and the Dividends URL is the "d&ignore" vs. the "v&ignore".

A simple web query won't work because the tables, as displayed on yahoo, span multiple "pages", and also include items that are not included in the files that I download.

Ideally the macro should use the stock I enter in cell C2 of the "Analysis" tab.

I'm sure that the solution is relatively simple to someone with more skill than myself, but I'm completely lost. Can someone please help me out?

Thanks in advance,

Liosis

 

[John_w]

Thanks to your excellent description of the manual steps it was very easy to write the code to automate this. Try this in a new workbook with "Input" and "Analysis" sheet names and a stock symbol (e.g. CL) in cell C2 of the "Analysis" sheet.

Public Sub Import_Yahoo_Finance_Historical()
    
    Dim URL As String
    Dim dateParams As String
    
    'Date ranges from default earliest Yahoo start date (m/d/y) to current date (m/d/y)
    
    dateParams = "&a=0&b=3&c=1977&d=" & Month(Date) - 1 & "&e=" & Day(Date) & "&f=" & Year(Date)
    
    'Daily prices
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range("C2").Value & dateParams & "&g=d&ignore=.csv"
    
    With Worksheets("Input")
        With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("A1"))
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        .QueryTables(1).Delete
    End With

    'Dividends only
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range("C2").Value & dateParams & "&g=v&ignore=.csv"
    
    With Worksheets("Input")
        With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("I1"))
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        .QueryTables(1).Delete
    End With

End Sub

 

[Liosis]

John_w,

All I can say is THANK YOU, that works flawlessly! You have no idea the amount of time you just saved me going forward. I'm definitely not the sharpest tool in the shed, but I have learned a couple things along the way:

1. People are generally willing to help you out if you ask
2. And the easier you make it for them to help, the more likely it is that they will

Thanks again, this has been a huge help...I really appreciate it.

I'm assuming you do some freelance work from time to time...if so, please private message me your contact info as I may have some real projects down the road that can use a hired hand.

Thanks again,

Liosis

 

[John_w]

Hi Liosis - glad to be of assistance and thanks for your encouraging words and kind offer. PM sent.

 

[Liosis]

Thanks John...I received your PM.


Quick question..I've run into one issue with the code you provided above. If I enter a stock ticker symbol which is not a valid symbol on Yahoo Finance (this can happen often), I get a 1004 runtime error which when I debug, highlights the following line of code:


.Refresh BackgroundQuery:=False


Now that's not a big deal in and of itself, but if I then type in a valid symbol and run again, I get the same error which is problematic as I have to close w/o saving and re-open the file which was saved before I got the error to have the macro work again, and as a result, I lose the work I had performed up to the runtime error. Can you or anyone advise?


Thanks

 

[John_w]

... if I then type in a valid symbol and run again, I get the same error

I reproduced that problem but I don't know why it happens. Probably something to do with the IE cache/temporary files, which web queries use behind the scenes.

Try this code instead which uses a different method and should be faster and easier to write code to handle invalid symbols:

Public Sub XMLhttp_Import_Yahoo_Finance_Historical()

    Dim XMLhttp As Object
    Dim dateParams As String
    Dim URL As String
    Dim CSVrecords As Variant
    
    Set XMLhttp = CreateObject("Microsoft.XMLHTTP")
    
    'Date ranges from default earliest Yahoo start date (m/d/y) to current date (m/d/y)
    
    dateParams = "&a=0&b=3&c=1977&d=" & Month(Date) - 1 & "&e=" & Day(Date) & "&f=" & Year(Date)
    
    'Daily prices
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range("C2").Value & dateParams & "&g=d&ignore=.csv"
    
    With XMLhttp
        .Open "GET", URL, False
        .send
    End With
    
    If XMLhttp.Status = 200 Then
    
        'Successful response - write data starting at A1
        
        CSVrecords = Split(XMLhttp.ResponseText, vbLf)
        With Worksheets("Input").Range("A1").Resize(UBound(CSVrecords), 1)
            .Value = Application.Transpose(CSVrecords)
            .TextToColumns .Range("A1"), comma:=True
        End With
        
        'Dividends only (assume successful response if daily prices above worked) and write to I1
        
        URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Sheets("Analysis").Range("C2").Value & dateParams & "&g=v&ignore=.csv"
            
        With XMLhttp
            .Open "GET", URL, False
            .send
            CSVrecords = Split(.ResponseText, vbLf)
        End With
        
        With Worksheets("Input").Range("I1").Resize(UBound(CSVrecords), 1)
            .Value = Application.Transpose(CSVrecords)
            .TextToColumns .Range("A1"), comma:=True
        End With
        
    Else
    
        MsgBox "XMLhttp status: " & XMLhttp.Status & " - " & XMLhttp.statusText & vbCrLf & vbCrLf & URL
        
    End If

End Sub

 

[Liosis]

Perfect...thanks!