15
$\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$
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$
  • $\begingroup$ related: stackoverflow.com/q/5246843/884752 $\endgroup$ – faysou Apr 25 '14 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$ – Kelly Lowder Sep 1 '17 at 21:21
  • $\begingroup$ Quandl still works though. $\endgroup$ – faysou Sep 2 '17 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$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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