Querying APIs
This short tutorial shows how to get free financial data from web APIs directly into Excel. We will show in particular how to get historical stock prices from Yahoo Finance and Quandl.
At the Ressources section of this page (bottom) you will find Excel files ready to be used (mathlayer® has to be loaded before opening these files for the function calls to work properly).
Note also that the examples presented hereafter use urlread function to query web APIs: in case you are using a proxy, you will need to specify the proxy settings as explained in urlread documentation.
Note also that the examples presented hereafter use urlread function to query web APIs: in case you are using a proxy, you will need to specify the proxy settings as explained in urlread documentation.
Yahoo Finance
Let's start by querying Yahoo Finance API to get historical data on Google stock:
>> strGOOG = urlread('https://ichart.yahoo.com/table.csv?s=GOOG&a=6&b=01&c=2016')#
Date,Open,High,Low,Close,Volume,Adj Close
2016-07-11,708.049988,716.51001,707.23999,715.090027,1104500,715.090027
2016-07-08,699.50,705.710022,696.434998,705.630005,1571400,705.630005
2016-07-07,698.080017,698.200012,688.215027,695.359985,1303100,695.359985
2016-07-06,689.97998,701.679993,689.090027,697.77002,1408600,697.77002
2016-07-05,696.059998,696.940002,688.880005,694.950012,1446100,694.950012
2016-07-01,692.200012,700.650024,692.130005,699.210022,1342700,699.210022
Yahoo API has returned a csv string. To start manipulating the data we can convert this string into a table object using function readtable as follows:
>> tblGOOG = readtable(strGOOG,'isstring',true)
tblGOOG(:,1:5)#
Date Open High Low Close
2016-07-11 708.05 716.51 707.24 715.09
2016-07-08 699.5 705.71 696.435 705.63
2016-07-07 698.08 698.2 688.215 695.36
2016-07-06 689.98 701.68 689.09 697.77
2016-07-05 696.06 696.94 688.88 694.95
2016-07-01 692.2 700.65 692.13 699.21
and from there easily visualize the data:
>> o = struct('markerSize',2)
o.xAxisLabels = cast(tblGOOG.Date,'cell')
o.xAxisTicks = 6
o.title = 'GOOG Stock Close Price - Source: Yahoo'
plot(tblGOOG.AdjClose,o)
Quandl
If you need work with free financial data then Quandl seems currently to be the best solution.
You will first need to create an account which will generate a key allowing in turn unlimited data download from Quandl free datasets.
Generating requests is straightforward and well documented:
> first you lookup for a ticker in Quandl website
> then you just need the information of the database and the identifier and you're done
If we look up for instance for Google we find - among others - in the database "WIKI" the identifier "GOOG".
We can then follow the steps described in the first example:
You will first need to create an account which will generate a key allowing in turn unlimited data download from Quandl free datasets.
Generating requests is straightforward and well documented:
> first you lookup for a ticker in Quandl website
> then you just need the information of the database and the identifier and you're done
If we look up for instance for Google we find - among others - in the database "WIKI" the identifier "GOOG".
We can then follow the steps described in the first example:
>> strGOOG = urlread('https://www.quandl.com/api/v1/datasets/WIKI/GOOG.csv?trim_start=2016-7-01')
tblGOOG = readtable(strGOOG,'isstring',true)
tblGOOG(:,1:5)#
Date Open High Low Close
2016-07-12 719.12 722.94 715.91 720.64
2016-07-11 708.05 716.51 707.24 715.09
2016-07-08 699.5 705.71 696.435 705.63
2016-07-07 698.08 698.2 688.215 695.36
2016-07-06 689.98 701.68 689.09 697.77
2016-07-05 696.06 696.94 688.88 694.49
2016-07-01 692.2 700.65 692.13 699.21