Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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, can someone help me understand what all these junk signs in the request?

like image 964
Curnelious Avatar asked Aug 02 '14 17:08

Curnelious


1 Answers

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! )

like image 154
user3666197 Avatar answered Sep 19 '22 05:09

user3666197