Download file from Website

Question

I am using Excel 2011 on Mac, meaning I am not able (or at least I do not know how to) navigate to a webpage and save data from a download link into an Excel Workbook. On this website,

https://finance.yahoo.com/quote/SPY/history?period1=1342681200&period2=1500447600&interval=1d&filter=history&frequency=1d

  Sub Test()

  Dim ws As Worksheet
  Dim qr As QueryTable
  Dim URL As String


  Set ws = Worksheets("Sheet1")

  Sheets("Sheet1").Cells.Clear
  URL = "https://finance.yahoo.com/quote/SPY/history?period1=1342681200&period2=1500447600&interval=1d&filter=history&frequency=1d"

  Set qr = ws.QueryTables.Add( _
      Connection:="URL;" & URL, _
      Destination:=Range("A1"))
      qr.RefreshStyle = xlOverwriteCells = True
      qr.BackgroundQuery = True
      qr.SaveData = True
      qr.Refresh BackgroundQuery = False


  End Sub

There is a "download data" button. I am trying to put this data into Excel so I can then calculate the 5 year standard deviation. Thank you!!

One of the problems I am having is that you have to scroll down in order to view all of the dates, so when I input the data into a querytable, it will only pull the data from the dates that are showing at first.

the meaning of your initial remarks is not clear in my mind. are you able to use a **web browser** and download the file by clicking on the _download data_ link on the yahoo web page? ... if yes, can you use **excel** to open the resulting csv file? if you have not tried, then please, open the file using excel. you should see the data in a worksheet — jsotola, Jul 19 '17 at 23:36
I'm not sure if I am understanding your question properly, but my idea is that once I have the 5 years in there, I can then just have each consecutive day be pasted in there. So this list would be updated daily. For example, tomorrow I would run this again in order to pull the data for 7/20/2017 in there. — elguapo, Jul 19 '17 at 23:41
Oh I see your question. My idea is to be able to skip that process of opening the web browser every day and having to open the csv file, and instead, to have excel download it automatically and paste in the new day and closing price of SPY — elguapo, Jul 19 '17 at 23:42
i understand. i checked the download link. today it is `https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=1463461200&period2=1494910800&interval=1d&events=history&crumb=aGnIEkELa61`. i suspect that _period1_ and _crumb_ parameters will change daily. could use _XMLHTTP_ to download the file and then parse it .... there must be an _XMLHTTP_ library available for a Mac. here is an example of pulling data from web https://stackoverflow.com/questions/25488687/parse-html-content-in-vba#25493388 — jsotola, Jul 20 '17 at 04:27
https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=0&period2=1494910800&interval=1d&events=history&crumb=aGnIEkELa61 adjusting _period1_ parameter moves the start date to january 29, 1993 — jsotola, Jul 20 '17 at 04:38
Thanks for the information! I'll play around with the URL and see if I can move the start date to 5 years back. I'm not familiar with XMLHTTP but I will look into it. — elguapo, Jul 20 '17 at 04:49
my bad. XMLHTTP appears to be windows based. which web browser are you using? — jsotola, Jul 20 '17 at 05:03
I mainly use google chrome on it, but I can use mozilla or safari. I don't have access to my laptop until tomorrow, but I will check out that link and test it out! Thanks for the info! — elguapo, Jul 20 '17 at 05:24
here is some code for a Mac https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac-2011 — jsotola, Jul 20 '17 at 05:33

ASH · Answer 1 · 2017-07-20T15:12:44.773

Ok, I understand now. Check out the link below.

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

Download the file from the link titled 'Get Excel Spreadsheet to Download Bulk Historical Stock Data from Google Finance'. That will give you what you want. I'll post the code here, but it's pretty extensive, and may not necessarily be helpful.

'Samir Khan
'simulationconsultant@gmail.com
'The latest version of this spreadsheet can be downloaded from http://investexcel.net/multiple-stock-quote-downloader-for-excel/
'Please link to http://investexcel.net if you like this spreadsheet


Sub DownloadStockQuotes(ByVal stockTicker As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal DestinationCell As String, ByVal freq As String)

Dim qurl As String
Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear As String

qurl = "http://finance.google.com/finance/historical?q=" & stockTicker
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
       "+" & Day(StartDate) & "+" & Year(StartDate) & _
       "&enddate=" & MonthName(Month(EndDate), True) & _
       "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

On Error GoTo ErrorHandler:

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

ErrorHandler:

End Sub

Sub DownloadData()

Dim frequency As String
Dim numRows As Integer
Dim lastRow As Integer
Dim lastErrorRow As Integer
Dim lastSuccessRow As Integer
Dim stockTicker As String
Dim numStockErrors As Integer
Dim numStockSuccess As Integer

numStockErrors = 0
numStockSuccess = 0

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

lastErrorRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
lastSuccessRow = ActiveSheet.Cells(Rows.Count, "L").End(xlUp).Row

ClearErrorList lastErrorRow
ClearSuccessList lastSuccessRow

lastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
frequency = Worksheets("Parameters").Range("b7")

'Delete all sheets apart from Parameters sheet
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name <> "Parameters" And ws.Name <> "About" Then ws.Delete
Next

Application.DisplayAlerts = True

'Loop through all tickers
For ticker = 12 To lastRow

    stockTicker = Worksheets("Parameters").Range("$a$" & ticker)

    If stockTicker = "" Then
        GoTo NextIteration
    End If

    Sheets.Add After:=Sheets(Sheets.Count)

    If InStr(stockTicker, ":") > 0 Then
        ActiveSheet.Name = Replace(stockTicker, ":", "")
    Else
        ActiveSheet.Name = stockTicker
    End If

    Cells(1, 1) = "Stock Quotes for " & stockTicker
    Call DownloadStockQuotes(stockTicker, Worksheets("Parameters").Range("$b$5"), Worksheets("Parameters").Range("$b$6"), "$a$2", frequency)
    Columns("a:a").TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
                                 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                 Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))


    If InStr(stockTicker, ":") > 0 Then
        stockTicker = Replace(stockTicker, ":", "")
    End If

    Sheets(stockTicker).Columns("A:G").ColumnWidth = 10

    lastRow = Sheets(stockTicker).UsedRange.Row - 2 + Sheets(stockTicker).UsedRange.Rows.Count

    If lastRow < 3 Then
        Application.DisplayAlerts = False
        Sheets(stockTicker).Delete
        numStockErrors = numStockErrors + 1
        ErrorList stockTicker, numStockErrors
        GoTo NextIteration
        Application.DisplayAlerts = True
    Else
        numStockSuccess = numStockSuccess + 1
        If Left(stockTicker, 1) = "^" Then
            SuccessList Replace(stockTicker, "^", ""), numStockSuccess
        Else
            SuccessList stockTicker, numStockSuccess
        End If
    End If

    Sheets(stockTicker).Sort.SortFields.Add Key:=Range("A3:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets(stockTicker).Sort
        .SetRange Range("A2:G" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Range("a3:a" & lastRow).NumberFormat = "yyyy-mm-dd;@"

    'Delete final blank row otherwise will get ,,,, at bottom of CSV
    Sheets(stockTicker).Rows(lastRow + 1 & ":" & Sheets(stockTicker).Rows.Count).Delete

    'Remove initial ^ in ticker names from Sheets
    If Left(stockTicker, 1) = "^" Then
        ActiveSheet.Name = Replace(stockTicker, "^", "")
    Else
        ActiveSheet.Name = stockTicker
    End If

    'Remove hyphens in ticker names from Sheet names, otherwise error in collation
    If InStr(stockTicker, "-") > 0 Then
        ActiveSheet.Name = Replace(stockTicker, "-", "")
    End If


NextIteration:
Next ticker

Application.DisplayAlerts = False

If Sheets("Parameters").Shapes("WriteToCSVCheckBox").ControlFormat.Value = xlOn Then
    On Error GoTo ErrorHandler:
    Call CopyToCSV
End If

If Sheets("Parameters").Shapes("CollateDataCheckBox").ControlFormat.Value = xlOn Then
    On Error GoTo ErrorHandler:
    Call CollateData
End If

ErrorHandler:

Worksheets("Parameters").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Worksheets("Parameters").Select
For Each C In ThisWorkbook.Connections
    C.Delete
Next

End Sub
Sub CollateData()

Dim ws As Worksheet
Dim i As Integer, first As Integer
Dim maxRow As Integer
Dim maxTickerWS As Worksheet

maxRow = 0
For Each ws In Worksheets
    If ws.Name <> "Parameters" Then
        If ws.UsedRange.Rows.Count > maxRow Then
            maxRow = ws.UsedRange.Rows.Count
            Set maxTickerWS = ws
        End If
    End If
Next

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Open"

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "High"

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Low"

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Close"

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Volume"

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Adjusted Close"

i = 1
maxTickerWS.Range("A2", "B" & maxRow).Copy Destination:=Sheets("Open").Cells(1, i)
Sheets("Open").Cells(1, i + 1) = maxTickerWS.Name

maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("High").Cells(1, i)
maxTickerWS.Range("c2", "c" & maxRow).Copy Destination:=Sheets("High").Cells(1, i + 1)
Sheets("High").Cells(1, i + 1) = maxTickerWS.Name

maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Low").Cells(1, i)
maxTickerWS.Range("d2", "d" & maxRow).Copy Destination:=Sheets("Low").Cells(1, i + 1)
Sheets("Low").Cells(1, i + 1) = maxTickerWS.Name

maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Close").Cells(1, i)
maxTickerWS.Range("e2", "e" & maxRow).Copy Destination:=Sheets("Close").Cells(1, i + 1)
Sheets("Close").Cells(1, i + 1) = maxTickerWS.Name

maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Volume").Cells(1, i)
maxTickerWS.Range("f2", "f" & maxRow).Copy Destination:=Sheets("Volume").Cells(1, i + 1)
Sheets("Volume").Cells(1, i + 1) = maxTickerWS.Name

maxTickerWS.Range("A2", "a" & maxRow).Copy Destination:=Sheets("Adjusted Close").Cells(1, i)
maxTickerWS.Range("g2", "g" & maxRow).Copy Destination:=Sheets("Adjusted Close").Cells(1, i + 1)
Sheets("Adjusted Close").Cells(1, i + 1) = maxTickerWS.Name

i = i + 2

For Each ws In Worksheets

    If ws.Name <> "Parameters" And ws.Name <> "About" And ws.Name <> maxTickerWS.Name And ws.Name <> "Open" And ws.Name <> "High" And ws.Name <> "Low" And ws.Name <> "Close" And ws.Name <> "Volume" And ws.Name <> "Adjusted Close" Then

        Sheets("Open").Cells(1, i) = ws.Name
        Sheets("Open").Range(Sheets("Open").Cells(2, i), Sheets("Open").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",2,0)"

        Sheets("High").Cells(1, i) = ws.Name
        Sheets("High").Range(Sheets("High").Cells(2, i), Sheets("High").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",3,0)"

        Sheets("Low").Cells(1, i) = ws.Name
        Sheets("Low").Range(Sheets("Low").Cells(2, i), Sheets("Low").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",4,0)"

        Sheets("Close").Cells(1, i) = ws.Name
        Sheets("Close").Range(Sheets("Close").Cells(2, i), Sheets("Close").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",5,0)"

        Sheets("Volume").Cells(1, i) = ws.Name
        Sheets("Volume").Range(Sheets("Volume").Cells(2, i), Sheets("Volume").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",6,0)"

        Sheets("Adjusted Close").Cells(1, i) = ws.Name
        Sheets("Adjusted Close").Range(Sheets("Adjusted Close").Cells(2, i), Sheets("Adjusted Close").Cells(maxRow - 1, i)).Formula = _
        "=vlookup(A2," & ws.Name & "!A$2:G$" & maxRow & ",7,0)"

        i = i + 1

    End If
Next

On Error Resume Next

Sheets("Open").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
Sheets("Close").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
Sheets("High").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
Sheets("Low").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
Sheets("Volume").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear
Sheets("Adjusted Close").UsedRange.SpecialCells(xlFormulas, xlErrors).Clear

On Error GoTo 0

Sheets("Open").Columns("A:A").EntireColumn.AutoFit
Sheets("High").Columns("A:A").EntireColumn.AutoFit
Sheets("Low").Columns("A:A").EntireColumn.AutoFit
Sheets("Close").Columns("A:A").EntireColumn.AutoFit
Sheets("Volume").Columns("A:A").EntireColumn.AutoFit
Sheets("Adjusted Close").Columns("A:A").EntireColumn.AutoFit
End Sub

Sub SuccessList(ByVal stockTicker As String, ByVal numStockSuccess As Integer)

Sheets("Parameters").Range("L" & 10 + numStockSuccess) = stockTicker

Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalDown).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalUp).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeLeft).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeTop).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeBottom).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeRight).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideVertical).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalDown).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlDiagonalUp).LineStyle = xlNone

With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With

Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideVertical).LineStyle = xlNone
Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Borders(xlInsideHorizontal).LineStyle = xlNone

With Sheets("Parameters").Range("L10:L" & 10 + numStockSuccess).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With

End Sub

Sub ErrorList(ByVal stockTicker As String, ByVal numStockErrors As Integer)

Sheets("Parameters").Range("J" & 10 + numStockErrors) = stockTicker

Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalDown).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalUp).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeLeft).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeTop).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeBottom).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeRight).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideVertical).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalDown).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlDiagonalUp).LineStyle = xlNone

With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With

Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideVertical).LineStyle = xlNone
Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Borders(xlInsideHorizontal).LineStyle = xlNone

With Sheets("Parameters").Range("J10:J" & 10 + numStockErrors).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With

End Sub

Sub ClearErrorList(ByVal lastErrorRow As Integer)
If lastErrorRow > 10 Then
    Worksheets("Parameters").Range("J11:J" & lastErrorRow).Clear
    With Sheets("Parameters").Range("J10").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("J10").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("J10").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("J10").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
End If
End Sub

Sub ClearSuccessList(ByVal lastSuccessRow As Integer)
If lastSuccessRow > 10 Then
    Worksheets("Parameters").Range("L11:L" & lastSuccessRow).Clear
    With Sheets("Parameters").Range("L10").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("L10").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("L10").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets("Parameters").Range("L10").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
End If
End Sub


Sub CopyToCSV()

Dim MyPath As String
Dim MyFileName As String

dateFrom = Worksheets("Parameters").Range("$b$5")
dateTo = Worksheets("Parameters").Range("$b$6")
frequency = Worksheets("Parameters").Range("$b$7")
MyPath = Worksheets("Parameters").Range("$b$8")

For Each ws In Worksheets
    If ws.Name <> "Parameters" And ws.Name <> "About" Then
        ticker = ws.Name
        MyFileName = ticker & " " & Format(dateFrom, "dd-mm-yyyy") & " - " & Format(dateTo, "dd-mm-yyyy") & " " & frequency
        If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
        If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
        Sheets(ticker).Copy
        With ActiveWorkbook
            .SaveAs Filename:= _
                    MyPath & MyFileName, _
                    FileFormat:=xlCSV, _
                    CreateBackup:=False
            .Close False
        End With
    End If
Next

End Sub

Hmm, that was part of the issue I was having. I don't understand that because they have a filter tab where you can change the date to go back 5 years. — elguapo, Jul 20 '17 at 04:50
@ryguy72, that URL is a great find. you can use _http://finance.google.com/finance/historical?q=SPY&startdate=Jan+01+1970&enddate=Dec+31+2050&output=csv_ , and it will pull down everything back to 2001 without calculating start and end dates — jsotola, Jul 20 '17 at 20:23
@ryguy72 Hey! Thanks for this, this made everything so much easier! — elguapo, Jul 20 '17 at 23:14

score 1 · Answer 2 · answered Jul 20 '17 at 06:11

here is all the info that i have gathered

first link: go to https://finance.yahoo.com/quote/SPY/history?period1=0&period2=1900000000&interval=1d&filter=history&frequency=1d

i changed the start date to zero and end date to 1900000000, which is jan 01, 1970 to march 17, 2030. (unix timestamps)

right click on "download data" link ... copy link address

that gives second link: https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=0&period2=1900000000&interval=1d&events=history&crumb=aGnIEkELa61

navigating to that URL returns a SPY.csv file spanning jan 29, 1993 to today (today being the actual today until 2030)

your program will have to go to that URL (second link) every time you need the fresh data (use cURL for that), and download the csv file into memory and save it.

now the catch, the download URL is not in cleartext in the html source of the first link.

this is what your program has to do: ( it will not be using a web browser, but cURL, which is a program that can request data from a web server)

you do these steps so that you know what is involved

open first link using google chrome ... right click ... view page source

copy and paste the source into a text editor

search for the crumb value (aGnIEkELa61 in the second link)(btw: it is a cookie)

this shows you where to find the crumb ( your program would not know the value of the crumb, it would have to find it here)

assemble the query URL ...

url = "https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=0&period2=1900000000&interval=1d&events=history&crumb=" & crumbValue

navigate to the resulting URL and download csv text data and process it

recap:

all downloads done by cURL which is called by the script

download first link into memory and parse (look for value of crumb)

use crumb to compute another URL (csv data)

download data from the computed url into memory and parse into tables (or save and import ... probably easier)

Hey thanks for the information! But someone else mentioned that the crumb may change everyday, and i think that's another issue I'm having since after I clicked on your second link, it says code: unauthorized and invalid cookie. I'm not too familiar with VBA just yet, so it will take me a while to figure out the code for all this, but I will try it and get back to you! Thanks!! — elguapo, Jul 20 '17 at 16:13
that is why you have to access the first link in order to get an updated crumb value for that day and then assemble the link for the actual data — jsotola, Jul 20 '17 at 19:58
the second link works only for me because the crumb is a cookie issued only to me. your crumb value is different — jsotola, Jul 20 '17 at 20:03
Ohh I see, that makes a lot more sense, I wasn't exactly sure what a crumb was or how it worked before. Thank you!! — elguapo, Jul 20 '17 at 20:06
using chrome browser ... go to first link ... press ctrl-U to view the source ... press ctrl-F to search ... type in "crumbstore" ... the crumb value is just to the right of the word "crumbstore" — jsotola, Jul 20 '17 at 20:07
i do not have access to a Mac, but i will try to piece together some code for you to try. — jsotola, Jul 20 '17 at 20:08
Hey I just got around to being able to read your original post, thanks! — elguapo, Jul 20 '17 at 23:17

score 1 · Accepted Answer · answered Jul 20 '17 at 21:04

i used some of the code posted by @ryguy72 and part from Open CSV file with correct data format for each column using TextFileColumnDataTypes? to get this minimal code as a test.

it works in windows, maybe it will work on a Mac (creates a table on currentsheet and fills it with stock data from the url in the program)

not all of the parameters in the With block are necessary, i think, but it is up to you to figure out which, by commenting out some and running the code (of course they can all be left in)

Sub DownloadStockQuotes()

    Dim qurl As String
    qurl = "http://finance.google.com/finance/historical?q=SPY&startdate=Jan+01+1970&enddate=Dec+31+2050&output=csv"

    Dim qt As QueryTable
    Set qt = ActiveSheet.QueryTables.Add(connection:="TEXT;" & qurl, Destination:=Range("b2"))

    With qt
        .Name = "Output"
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(2)         ' This doesn't make any difference anymore

        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
End Sub

Hey! Wow this worked perfectly, I just had to take out some of the parameters. Thank you! — elguapo, Jul 20 '17 at 23:20

Download file from Website

3 Answers3