Yahoo Stock Data Excel VBA Macro
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I am trying to pull end of day stock data by minute from Yahoo finance. I am looking for visual basic code to parse out the columns and delete all of the lines until you hit the first unix date. This is probably pretty simple but I am not seeing the forrest for the trees.

You can build this in an excel file using VBA and I can edit it for my needs from there.

Here is the URL that will pull the data by minute (this one pulls Apple Daily Data): http://chartapi.finance.yahoo.com/instrument/1.0/aapl/chartdata;type=quote;range=1d/csv

Specific deliverables: Looking for a VBA macro that will pull the data based on the above URL and load it to the excel file. That macro then should strip the top lines out until it hits the first UNIX Date/Time in column A.

If you have questions on this please let me know.

awarded to Wikimedia

Crowdsource coding tasks.

2 Solutions


Option Explicit
Option Compare Text    
Sub test() 
  MsgBox GetTitle("http://chartapi.finance.yahoo.com/instrument/1.0/aapl/chartdata;type=quote;range=1d/csv", "Title") 
End Sub
Function GetTitle(WebPage As String, Tag As String) 

Dim t, Tag2 As String, EndTag As String 
Dim oHttp As Object, txt$, i&, j& 

 'Adjust this to suit
Tag2 = Tag & "=" & Chr(34) 
EndTag = Chr(34) 


On Error Resume Next 
Set oHttp = CreateObject("MSXML2.XMLHTTP") 
If Err <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest") 
If oHttp Is Nothing Then MsgBox "MSXML2.XMLHTTP not found", 16, "Error": Exit Function 
On Error Goto 0 

With oHttp 
    .Open "GET", WebPage, False 
    .Send 
    txt = .responseText 
    i = InStr(1, txt, Tag2, 1) 
    If i = 0 Then 
        GetTitle = Tag & " not found" 
    Else 
        t = Split(txt, Tag2)(1) 
        GetTitle = Split(t, EndTag)(0) 
    End If 
End With 
Set oHttp = Nothing 

End Function 
I gave this a try and it does not work - Please provide specific instructions to ensure I am working it correctly - I so I will mark as solved for payment.
subzero over 6 years ago

Sub Getdata()
ConnectStr = "URL;http://chartapi.finance.yahoo.com/instrument/1.0/aapl/chartdata;type=quote;range=1d/csv"
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=Range("$A$1"))
    .Name = "StockPrices_" & StSymbol
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "20"
    .WebFormatting = xlWebFormattingNone
    .WebSingleBlockTextImport = False
    .Refresh
End With

Copy this code into module in you vba then press run or you can set a timer to run automatically. and it will load data from the address to your specified address i.e. A1

I get an error when I try this that says "object doesn't support this property or method."
subzero over 6 years ago
Sorry Please add End sub at the end of the code!
Diouf over 6 years ago
Still it does not run. I still get the error even when added end sub
subzero over 6 years ago
View Timeline