Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Download a .csv file from a web page

Greetings,


I'm trying to download a .csv file from Yahoo into Excel. Using Visual Basic for Applications (VBA), I got it working in Excel 2010 for Windows. It doesn't work in Excel 2011 for Mac, however, because there is no WinHttp.WinHttpRequest object.


Is there an AppleScript method that would allow me to download a .csv file from a link on a web page? If so, I can execute it using VBA's MacScript function.


Thanks,

Shane.

Windows 7, Home Premium SP1 64-BIT

Posted on Jul 4, 2015 10:30 PM

Reply
5 replies

Jul 5, 2015 1:01 AM in response to CShaneG

Hello


You may simply use curl(1) to retrieve data.


E.g.,


Shell script:


#!/bin/bash curl -L -s 'http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv' > ~/desktop/quotes.csv



AppleScript:


set u to "http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv" do shell script "curl -L -s " & u's quoted form & " > ~/desktop/quotes.csv"



Regards,

H

Jul 5, 2015 5:12 AM in response to CShaneG

Here's what I've been using in my scripts:


to getYData(symb, qProp) -- get Yahoo! data

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set symbStr to "s=" & symb

set propStr to "&f=" & qProp

do shell script "curl -s " & "'" & baseURL & symbStr & propStr & "'"

end getYData



However, you can do it directly in VBA on the Mac without WinHttp.WinHttpRequest with something like this:



' Request the data from Yahoo! and put the returned CSV in rows in dDest's column

With ActiveSheet.QueryTables.Add(Connection:="URL;" & Y_API_URL, Destination:=dDest)

.Refresh BackgroundQuery:=False

.RefreshStyle = xlOverwriteCells

.SaveData = True

EndWith


Where Y_API_URL is the assembled base url followed by symbols and the properties you want, and dDest is defined with something like this:


Dim dDest As Range: Set dDest = Range("C3")


Then you can split the csv with something like this:


' Split the CSV text into columns -- similar to the 'Text to Columns' wizard in the Excel Ribbon

Dim CsvCol As Range: Set CsvCol = Range(dDest.Offset(0, 0), dDest.Offset(0, 0).End(xlDown))

CsvCol.TextToColumns Destination:=dDest, DataType:=xlDelimited, comma:=True



The works well in Excel 2011, Excel 2016 (the public beta) and in various Excel 2013 for Windows, and probably earlier versions. So you shouldn't need to use the MacScript function.


SG

Download a .csv file from a web page

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.