3

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.

Improve this question
1
  • Thanks I got the code to work, great! I got the code to work by modifying the following: For R = 0 To UBound(oArray) AND oArray = Split(sResult, vbLf) Commented Aug 21, 2013 at 12:39

2 Answers 2

Reset to default
3

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.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The problem is before the splitting part. That is if you download the document manually more data is contained in that file (number of characters is like 200k) vs the sResult variable which stores = XMLHTTP.ResponseText. (where number of characters is like 18k) Any ideas?
I have just tested the code and download from Chrome, the length is the same (188066 at time of this comment, in 3927 lines).
How did you test the code? I cannot get this line to work? oData = Split(oResult(R), ",")
0

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)

Improve this answer

Comments