yahoo-financealgorithmic-trading

Getting stock's historical data


We would like to check on stock's historical data, using HTTP request, and get JSON.

Using the yahoo API ,I found it hard to not only clearly understand the HTTP request fields, but also to get the data of a certain day (not average for each day, but the values during a certain day), with this :

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20%3D%20%22AAPL%22%20and%20startDate%20%3D%20%222012-09-11%22%20and%20endDate%20%3D%20%222014-02-11%22&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=

  1. There is no explanation anywhere of how to set each field (also not at Yahoo console).

  2. You can't set a certain day, and get its values.

Is there a stable solution other than Yahoo which is now free? Or, how can I understand what all these junk signs in the request?


Solution

  • Ad-hypothesis in 2.: "You can't set a certain day, and get his values" ... well, YOU CAN:

    The <URL> per-se contains data selection tags included:

    http://query.yahooapis.com/v1/public/yql?q=
    select * from   yahoo.finance.historicaldata
             where  symbol    = "AAPL"
             and    startDate = "2012-09-11"
             and    endDate   = "2014-02-11"
    &format=json
    &diagnostics=true
    &env=store://datatables.org/alltableswithkeys
    &callback=
    

    so to get just the last few ( a pair of, notice the interpretation of endDate in results ) days between 2014-08-10 .. 2014-08-12 on "AAPL":


                    "results":{
                                "quote":[
                                         {
                                           "Symbol":"AAPL",
                                           "Date":"2014-08-12",
                                           "Open":"96.04",
                                           "High":"96.88",
                                           "Low":"95.61",
                                           "Close":"95.97",
                                           "Volume":"33795000",
                                           "Adj_Close":"95.97"
                                          },
                                         {
                                           "Symbol":"AAPL",
                                           "Date":"2014-08-11",
                                           "Open":"95.27",
                                           "High":"96.08",
                                           "Low":"94.84",
                                           "Close":"95.99",
                                           "Volume":"36585000",
                                           "Adj_Close":"95.99"
                                          }
                                        ]
                               }
    

    ( a full Y! response transcript )

    { 
     "query":{
              "count":2,
              "created":"2014-08-14T21:32:41Z",
              "lang":"en-EN",
              "diagnostics":{
                             "url":[
                                    { 
                                      "execution-start-time":"0",
                                      "execution-stop-time":"1",
                                      "execution-time":"1",
                                     "content":"http://www.datatables.org/yahoo/finance/yahoo.finance.historicaldata.xml"
                                     },
                                   {
                                     "execution-start-time":"5",
                                     "execution-stop-time":"16",
                                     "execution-time":"11",
                                     "content":"http://ichart.finance.yahoo.com/table.csv?g=d&f=2014&e=12&c=2014&b=10&a=7&d=7&s=AAPL"
                                    },
                                   {
                                     "execution-start-time":"18",
                                     "execution-stop-time":"28",
                                     "execution-time":"10",
                                     "content":"http://ichart.finance.yahoo.com/table.csv?g=d&f=2014&e=12&c=2014&b=10&a=7&d=7&s=AAPL"
                                    }
                                   ],
                                   "publiclyCallable":"true",
                                   "cache":[
                                             {
                                               "execution-start-time":"4",
                                               "execution-stop-time":"4",
                                               "execution-time":"0",
                                               "method":"GET",
                                               "type":"MEMCACHED",
                                               "content":"91a0664b4e7cf29d40cce123239fec85"
                                              },
                                             {
                                               "execution-start-time":"17",
                                               "execution-stop-time":"18",
                                               "execution-time":"1",
                                               "method":"GET",
                                               "type":"MEMCACHED",
                                               "content":"31dd9633be8581af77baa442f314c921"
                                              }
                                            ],
                                   "query":[
                                            {
                                              "execution-start-time":"5",
                                              "execution-stop-time":"17",
                                              "execution-time":"12",
                                              "params":"{url=[http://ichart.finance.yahoo.com/table.csv?g=d&f=2014&e=12&c=2014&b=10&a=7&d=7&s=AAPL]}",
                                              "content":"select * from csv(0,1) where url=@url"
                                             },
                                            {
                                              "execution-start-time":"18",
                                              "execution-stop-time":"28",
                                              "execution-time":"10",
                                              "params":"{columnsNames=[Date,Open,High,Low,Close,Volume,Adj_Close], url=[http://ichart.finance.yahoo.com/table.csv?g=d&f=2014&e=12&c=2014&b=10&a=7&d=7&s=AAPL]}",
                                              "content":"select * from csv(2,0) where url=@url and columns=@columnsNames"
                                             }
                                           ],
                           "javascript":{
                                          "execution-start-time":"3",
                                          "execution-stop-time":"29",
                                          "execution-time":"25",
                                          "instructions-used":"34359",
                                          "table-name":"yahoo.finance.historicaldata"
                                         },
                           "user-time":"31",
                           "service-time":"23",
                           "build-version":"0.2.2666"
                         },
                "results":{
                            "quote":[
                                     {
                                       "Symbol":"AAPL",
                                       "Date":"2014-08-12",
                                       "Open":"96.04",
                                       "High":"96.88",
                                       "Low":"95.61",
                                       "Close":"95.97",
                                       "Volume":"33795000",
                                       "Adj_Close":"95.97"
                                      },
                                     {
                                       "Symbol":"AAPL",
                                       "Date":"2014-08-11",
                                       "Open":"95.27",
                                       "High":"96.08",
                                       "Low":"94.84",
                                       "Close":"95.99",
                                       "Volume":"36585000",
                                       "Adj_Close":"95.99"
                                      }
                                    ]
                           }
          }
    }
    

    Nota Bene:

    One may detect, that the fully-fledged query processing re-wraps data-source request sourced and post-processed from just:

    http://ichart.finance.yahoo.com/table.csv?g=d&f=2014&e=12&c=2014&b=10&a=7&d=7&s=AAPL

    yielding:

    Date,Open,High,Low,Close,Volume,Adj Close
    2014-08-12,96.04,96.88,95.61,95.97,33795000,95.97
    2014-08-11,95.27,96.08,94.84,95.99,36585000,95.99
    

    Per aspera Ad Astra ... ( ... more GHz, more TB, more Gbps, more ..., more ..., Moore! )