excel vba http request download data from yahoo finance

Question

I am in the process of making a program I wrote using excel vba faster.

The program downloads stock market data from the asx.

I want to get data from 2 urls:

MY CODE

url2 = "http://ichart.finance.yahoo.com/table.txt?s=bhp.ax"

Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

XMLHTTP.Open "GET", url2, False

XMLHTTP.send

result = XMLHTTP.responseText

ActiveCell.Value = result

Set XMLHTTP = Nothing

URL 1. http://ichart.finance.yahoo.com/table.txt?s=bhp.ax

MY PROBLEM.

This file is very large. I thought I could simply store the result of these http requests and print it to the debug window or directly to a cell. However these methods seem to be cutting off parts of the data?

if I download the txt file from url 2 in notepad++ it has almost 200 000 characters but it excel it has between 3 -5 000. What is the best way to handle these requests so that all the data is captured and I can parse it all later?

URL 2. from the first URL I only want the JSON data which results from the YQL query.

MY PROBLEM

I am not sure how to get just the json data when you follow the link below, and or how to store it so that the problem experienced with URL 1 (missing data) does not occur.

http://developer.yahoo.com/yql/console/?q=select%20symbol%2C%20ChangeRealtime%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22YHOO%22%2C%22AAPL%22%2C%22GOOG%22%2C%22MSFT%22%29%20|%20sort%28field%3D%22ChangeRealtime%22%2C%20descending%3D%22true%22%29%0A%09%09&env=http%3A%2F%2Fdatatables.org%2Falltables.env#h=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22bhp.ax%22%29

Many Thanks, Josh.

Answer 1

Try this revised code

Sub GetYahooFinanceTable()
    Dim sURL As String, sResult As String
    Dim oResult As Variant, oData As Variant, R As Long, C As Long

    sURL = "http://ichart.finance.yahoo.com/table.txt?s=bhp.ax"
    Debug.Print "URL: " & sURL
    sResult = GetHTTPResult(sURL)
    oResult = Split(sResult, vbLf)
    Debug.Print "Lines of result: " & UBound(oResult)
    For R = 0 To UBound(oResult)
        oData = Split(oResult(R), ",")
        For C = 0 To UBound(oData)
            ActiveSheet.Cells(R + 1, C + 1) = oData(C)
        Next
    Next
    Set oResult = Nothing
End Sub

Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function

This will split up the data into Rows so the max text length are not reached in a cell. Also this have further split the data with commas into corresponding columns.