excel vba http request download data from yahoo fi

问题:

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.

回答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.

回答2:

You may like to try following code from http://investexcel.net/importing-historical-stock-prices-from-yahoo-into-excel/

I just modify the qurl variable to your url and it work, it pouring 4087 line of data to my excel sheet, nicely formatted without any problem. Just name your sheet1 as Data.

    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
    Dim LastRow As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Sheets("Data").Cells.Clear

    Set DataSheet = ActiveSheet

'        StartDate = DataSheet.Range("startDate").Value
'        EndDate = DataSheet.Range("endDate").Value
'        Symbol = DataSheet.Range("ticker").Value
'        Sheets("Data").Range("a1").CurrentRegion.ClearContents

'        qurl = "http://ichart.finance.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=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
'            Symbol & "&x=.csv"


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

QueryQuote:
             With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

            Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False

         Sheets("Data").Columns("A:G").ColumnWidth = 12

    LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count

    Sheets("Data").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets("Data").Sort
        .SetRange Range("A1:G" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        .SortFields.Clear
    End With

End Sub

(the above is not my code, it was taken from the excel file they posted on investexcel.net link above)