excel vba http请求从雅虎金融下载数据 [英] excel vba http request download data from yahoo finance

问题描述

我正在使用excel vba编写的程序。

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

该程序从asx中下载股票市场数据。

The program downloads stock market data from the asx.

我想从2个网址获取数据：

I want to get data from 2 urls:

我的代码

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。

此文件非常大。我以为我可以简单地存储这些http请求的结果，并将其打印到调试窗口或直接连接到单元格。但是，这些方法似乎正在切断部分数据？

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?

如果我从记事本中从url 2下载txt文件，它有近20万个字符
，它的优势在3 -5 000之间。处理这些请求的最好方法是什么，以便所有的数据被捕获，我可以稍后解析？

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.从第一个URL我只想要从YQL查询生成的JSON数据。

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

MY PROBLEM

我不知道如何获取json数据当您遵循下面的链接和/或如何存储它，以便不会发生URL 1（缺少数据）遇到的问题。

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％20英寸％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 =选择％20 * ％ 20from％20yahoo.finance.quotes％20where％20symb ol％20in％20％28％22bhp.ax％22％29

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

很多谢谢Josh。

推荐答案

尝试修改后的代码

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.

这篇关于excel vba http请求从雅虎金融下载数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！