16
$\begingroup$

How can I download historical data from Yahoo ? Although Mathematica's FinancialData function uses Yahoo it could be useful to have more control on how to retrieve data from Yahoo's YQL.

Edit Similarly how could I use Quandl ?

$\endgroup$
1

3 Answers 3

23
$\begingroup$

Here's a set of functions that allows to do this. The code uses many ideas found on this site and on other places on the web. It is a bit factorized already so it should be easily reusable.

More on YQL and available tables here:
https://developer.yahoo.com/yql
https://github.com/yql
Query test

Edit: this API is great also and simple http://www.quandl.com/help/api
You get usable data in MM and some basic data manipulation straight away using

Import@"http://www.quandl.com/api/v1/datasets/WIKI/AAPL.csv?sort_order=asc&exclude_headers=true&rows=3&trim_start=2012-11-01&trim_end=2013-11-30&column=4&collapse=quarterly&transformation=rdiff"  

The work below is still useful in order to access other types of data than financial ones in YQL.
Here are usage examples

symbol="YHOO";
symbols={"YHOO","GE"};
startDate={2013,04,13};
endDate={2014,04,16};

GetYahooMultiQuote[symbol,startDate,endDate]
GetYahooMultiQuote[symbols,startDate,endDate,{"Adj_Close","High"}]

Note that if you request too much data you won't be able to query too many symbols at the same time. But it's still quite practical to be able to get data for many symbols in only one request for a small historical time range.

toYahooString[string_String]:="\""<>string<>"\"";
toYahooDate[date_List]:=DateString[date,{"Year", "-","Month","-","Day"}]//toYahooString;
toYahooList[list_List]:=StringJoin@@Riffle[toYahooString/@list,","];
toYahooUrl[sqlRequest_String]:=
    Module[{urlRules},

        urlRules={" "->"%20","\""->"%22","("->"%28",")"->"%29"};

        StringReplace[
            "http://query.yahooapis.com/v1/public/yql?env=store://datatables.org/alltableswithkeys&format=json&q="
            <>sqlRequest
            ,
            urlRules
        ]
    ];
toYahooRequest[sqlRequest_]:=sqlRequest//toYahooUrl//Import[#,"JSON"]&;
doAndParseYahooRequest[sqlRequest_,yahooResultField_,requiredFields_,nsymbols_]:=
    Module[{jsonImport,quotes},

        jsonImport=sqlRequest//toYahooRequest;

        quotes=OptionValue[jsonImport,yahooResultField];

        requiredFields/.quotes // Partition[#,Length@#/nsymbols]& // If[Length@#==1,First@#,#]&
    ];

possibleQuotes=Alternatives@@{"Adj_Close","Close","Date","High","Low","Open","Symbol","Volume"};

ClearAll@GetYahooMultiQuote;
GetYahooMultiQuote[symbols_,startDate_,endDate_,quote:(possibleQuotes|{possibleQuotes..}):"Adj_Close"]:=
    Module[{symbols2=Flatten@{symbols},sqlRequest,sqlRequestModified},

        sqlRequest="select * from yahoo.finance.historicaldata where symbol in (SYMBOLS) and startDate = START_DATE and endDate = END_DATE";
        sqlRequestModified=
            StringReplace[
                sqlRequest
                ,
                {"SYMBOLS"->toYahooList@symbols2,"START_DATE"->toYahooDate@startDate,"END_DATE"->toYahooDate@endDate}
            ];

        doAndParseYahooRequest[sqlRequestModified,"query"->"results"->"quote",quote,Length@symbols2]
    ];

Edit

For Quandl the following function is useful. It implements all the importing options described in the help http://www.quandl.com/help/api. It works for a single set or a multiset. It includes memoization also.

(*"transformation=none|diff|rdiff|cumul|normalize"
"collapse=none|daily|weekly|monthly|quarterly|annual"
"sort_order=asc|desc"
"dataFormat=csv|json|xml"*)

ClearAll@GetQuandlData;
Options[GetQuandlData]={"Collapse"->"daily","Tansformation"->"none","Sort"->"asc","ExcludeHeaders"->True,"Column"->4,"DataFormat"->"csv","Token"->None};
GetQuandlData[ticker_,nData:({startDate_,endDate_}|rows_),opts:OptionsPattern[]]:= GetQuandlData[ticker,nData,opts]=
Module[{dataFormat,collapse,transformation,sort,url,excludeHeaders,column,token},
    {dataFormat,collapse,transformation,sort,excludeHeaders,column,token}=
        OptionValue[GetQuandlData,#]&/@{"DataFormat","Collapse","Tansformation","Sort","ExcludeHeaders","Column","Token"};

    url="https://www.quandl.com/api/v1/"~~
        If[ListQ@ticker,
            "multisets."~~
            dataFormat~~
            "?columns="~~
            StringJoin@@
                Riffle[
                    MapThread[
                        #1~~"."~~#2&
                        ,
                        {StringReplace[#,"/"->"."]&/@ticker,ToString/@If[ListQ@column,column,ConstantArray[column,Length@ticker]]}
                    ]
                    ,
                    ","
                ]
            ,
            "datasets/"~~
            ticker~~
            "."~~
            dataFormat~~
            "?"~~
            If[column===All,
                ""
                ,
                "column="~~
                ToString@column
            ]
        ]~~
        "&collapse="~~
        collapse~~
        If[ListQ@nData,
            "&trim_start="~~
            DateString[startDate,{"Year","-","Month","-","Day"}]~~
            "&trim_end="~~
            DateString[endDate,{"Year","-","Month","-","Day"}]
            ,
            "&rows="~~
            ToString@rows
        ]~~
        "&sort_order="~~
        sort~~
        "&transformation="~~
        transformation~~
        "&exclude_headers="~~
        ToString@excludeHeaders~~
        If[token===None,
            ""
            ,
            "&auth_token="~~
            token
        ];

    Import@url
];

GetQuandlData["YAHOO/INDEX_GSPC", {{2014, 08, 16}, {2014, 10, 16}}, "Tansformation"->"diff"]  
GetQuandlData["YAHOO/INDEX_VIX",10,"Sort"->"desc"]  
GetQuandlData[{"YAHOO/INDEX_GSPC", "YAHOO/INDEX_VIX"},10,"Sort"->"desc","Column"->{4,2}]  
GetQuandlData[{"YAHOO/INDEX_GSPC", "YAHOO/INDEX_VIX"},10,"Sort"->"desc","Column"->4]
$\endgroup$
3
  • $\begingroup$ related: stackoverflow.com/q/5246843/884752 $\endgroup$
    – faysou
    Apr 25, 2014 at 11:47
  • $\begingroup$ Unfortunately, as of approximately May 2017, this will no longer work. Will return The server returned the HTTP status code 400 ("Bad Request" $\endgroup$ Sep 1, 2017 at 21:21
  • $\begingroup$ Quandl still works though. $\endgroup$
    – faysou
    Sep 2, 2017 at 21:43
4
$\begingroup$

You can find QuandlLink package for Mathematica from following wolfram community post:

http://community.wolfram.com/groups/-/m/t/540228

or

https://github.com/bajracha71/Quandl-Mathematica-QuandlLink

$\endgroup$
4
$\begingroup$

Just for Quandl. You can use Quandl's API guide and customize the data. For example, first download historical data for FaceBook.

stDate = "2015-05-31";
endDate = "2017-05-31";
mysymbol = "WIKI/FB";
yourapikey = "..."; (*you need to supply your freely available quandl api key*)
data = Import[StringJoin["https://www.quandl.com/api/v3/datasets/", mysymbol, 
    "/data.csv?start_date=", stDate, "&end_date=", endDate, 
    "&order=asc&api_key=", yourapikey]];
First[data]

Then convert "Open", "High", "Low", "Close", "Volume" into a time seires.

dates = ToExpression[StringSplit[data[[2 ;;, 1]], "-"]];
datav = data[[2 ;;, {2, 3, 4, 5, 6}]];
datats = TimeSeries[datav, {dates}];

Then you can use TradingChart to chart the data.

TradingChart[datats]

enter image description here

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.