How to request financial data from Yahoo's YQL and Quandl?

Question

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 ?

Answer 1

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]

Answer 2

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

Answer 3

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]