Quantitative Analysis, Risk Management, Modelling, Algo Trading, and Big Data Analysis

Accessing a SQL Server database in Matlab and the use of FTS objects


Feeding our models with data for backtesting purposes requires pulling data from the server. If we do our modeling part in Matlab under Linux and the MS SQL Server database is our target, the quickest solution for the establishment of a good connectivity is making use of Java Database Connectivity (JDBC) driver. Firstly, visit the following web page to download and current driver (.tar.gz) and next follow the installation steps provided by Mathworks.

In the following example we will try to connect to our database called ‘XDataBase’ and retrieve stock prices from Feb 2, 1998 of Apple Inc. (NASDAQ ticker: AAPL).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ticker=['AAPL'];
start=['02-Feb-1998'];
 
% --Connect to your local SQL Server and fetch the data for a ticker
%
% define connection
conn=database('XDataBase','matlab','m4tl4b','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://163.118.2.7:1533;database=XDataBase');
 
% specify return format
setdbprefs('DataReturnFormat','structure');
 
% define your SQL query, as for example:
QueryString = ['SELECT sec.tic, dp.datadate, dp.prchd As PriceH, dp.prcld As PriceL, dp.prccd As PriceC, dp.prccd/dp.ajexdi As PriceCA, dp.cshtrd*dp.ajexdi As Vol FROM  Xpressfeed.dbo.security AS sec INNER JOIN Xpressfeed.dbo.sec_dprc AS dp ON sec.gvkey = dp.gvkey AND sec.iid = dp.iid AND dp.datadate >=''',start,''' WHERE (sec.tic IN (''',ticker,'''))'];
 
% execute connection, fetch data, and assign them to a Matlab variable
cur = exec(conn, QueryString);
myData = fetch(cur);
secData = myData.data;

In the above example, our structure variable of secData is composed of secData.PriceH, secData.PriceL, secData.PriceC, secData.PriceCA, and secData.Vol arrays containing information about AAPL’s High, Low, Close, Adjusted Close prices and Volume traded, respectively.

Making use of Matlab’s Financial Toolbox, one can store all these data into a useful text-like file format. We do it by construction of Financial Time-Series (FTS) object:

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
% verify the existence of data for a ticker
skip=false;
try
    n=length(secData.PriceC);  % a random test
catch
    skip=true;
end
if(~skip)
    dates=secData.datadate;
    dates=datenum(dates);
    data=[zeros(n,1) secData.PriceH secData.PriceL secData.PriceC secData.PriceCA secData.Vol];
    colheads={'DATES';'OPEN';'HIGH';'LOW';'CLOSE';'CLOSEA';'VOLUME'};
    desc=ticker;
    filename=[ticker,'.dat'];
    % save AAPL's extracted Price and Volume data into AAPL.dat file
    fts2ascii(filename,dates,data,colheads,desc);
 
    % construct FTS object by reading the data from the file
    fts=ascii2fts(filename,1,2);
    data=fts2mat(fts,1);
    % and plot the Adjusted Close Price
    scrsz = get(0,'ScreenSize'); 
    hh=figure('Position',[70 scrsz(4)/2 scrsz(3)/1.1 scrsz(4)/2],'Toolbar','none');
    plot(data(:,1),data(:,5)); 
    xlim([min(data(:,1)) max(data(:,1))]); 
    title(ticker);
    xlabel('Time [days]'); ylabel('Adjusted Close Price [US$]');
end

As the result of execution of code lines 28 to 35, we were able to retrieve AAPL stock data and save them into AAPL.dat file. First ten lines of the file are as follows:

$ head -10 AAPL.txt
AAPL
DATES 	OPEN 	HIGH 	LOW 	CLOSE 	CLOSEA 	VOLUME
02-Feb-1998   0.000000   18.500000   17.375000   17.688000   4.422000   22740000
03-Feb-1998   0.000000   18.625000   17.688000   18.313000   4.578250   14380000
04-Feb-1998   0.000000   18.500000   18.000000   18.250000   4.562500   6078400
05-Feb-1998   0.000000   18.500000   18.000000   18.313000   4.578250   8508000
06-Feb-1998   0.000000   18.688000   18.250000   18.500000   4.625000   7228000
09-Feb-1998   0.000000   19.500000   18.375000   19.188000   4.797000   17668000
10-Feb-1998   0.000000   19.563000   19.063000   19.438000   4.859500   15072000
11-Feb-1998   0.000000   19.500000   18.875000   19.000000   4.750000   7560000

In line 38 we show how to construct FTS object

>> whos fts
  Name         Size             Bytes  Class    Attributes
 
  fts       3366x6             190016  fints

whereas line 39 provides us with a transformation of the object’s data into matrix format that we may use to plot AAPL’s Adjusted Close Prices.

It is of great importance to remind that Financial Toolbox allows us to express a date (e.g. 11-Feb-1998) as a continuous number:

>> datenum('02-Feb-1998')
ans =
      729788

or reversely

>> datestr(ans)
ans =
02-Feb-1998
  • Irek

    Thank you for sharing the source code.

Contact Form Powered By : XYZScripts.com