Friday, February 24, 2012

Add database functionality to Matlab with SQLite.

Matlab data structures are fine for most of research work, but running a daily trading business is a different thing. A trader or an account manager often needs to maintain a list of trades, accounts, strategies, clients etc., often dealing with relational data An SQL database is ideal for such a task, but most solutions (like MySQL) are are quite an overkill, as we don't need a concurrent client database, data trees etc.
A solution comes in form  SQLite, a serverless database engine that stores the whole database in a single file. It is widely used in anything from mobile phones to mainframes and runs on almost anything.
Quite some time ago I have written a post on using SQLite in Matlab. As I am still a happy user, time for an update and a demo.
Installation: you only need to download and unzip the files from here:
(note: you'll probably need to compile from source on a 64bit system).

Demo: I have written a simple script to demonstrate how to keep a list of portfolio positions for thee separate accounts.


Copyright: Jev Kuznetsov 
License: BSD

demo of SQLite for portfolio management.


% test sqlite
clear all;

tables = mksqlite('show tables');

%% create a new data table 
mksqlite('DROP TABLE tbl_portfolios');
sql = 'CREATE TABLE tbl_portfolios ( id INTEGER PRIMARY KEY AUTOINCREMENT, accountName TEXT, symbol TEXT, position INTEGER)'; 

%% now add some random data 
symbols = {'ABC','DEF','GHI','XYZ','AAA','BBB','CCC','DDD'};
accounts = {'acct1','acct2','acct3'}; 

%mksqlite('PRAGMA synchronous=OFF'); % speed tweak, see sqlite doc
mksqlite('BEGIN'); % bundle multiple inserts  into one transaction, speed boost!
for i=1:100
    symbol = symbols{ceil(length(symbols)*rand)}; % pick a random symbol from symbols
    account = accounts{ceil(length(accounts)*rand)}; % same for account
    position = ceil(1000*rand); 
    fprintf('adding account: %s symbol:%s position:%i \n', symbol,account,position);
    % first, check if symbol is already in portfolio
    res = mksqlite(sprintf('SELECT id FROM tbl_portfolios WHERE accountName="%s" AND symbol="%s"',account,symbol));
    if isempty(res)
      fprintf('Adding symbol \n');
      mksqlite(sprintf('INSERT INTO tbl_portfolios (accountName, symbol, position) VALUES ("%s","%s",%i)',account,symbol,position));
      fprintf('Updating symbol \n');
      mksqlite(sprintf('UPDATE tbl_portfolios SET position=%i WHERE id=%i',position,;
%mksqlite('PRAGMA synchronous=NORMAL');


%% now pull the data from database

fprintf('\nGetting data from database\n');

res = mksqlite('SELECT * FROM tbl_portfolios ORDER BY accountName ASC');
for i=1:length(res)
  fprintf('%s\t%s\t%i\n', res(i).accountName, res(i).symbol,res(i).position);
%% try some handy sql stuff
% unique account names
res= mksqlite('SELECT DISTINCT accountName FROM tbl_portfolios') 
% sum of all positions in acct1
res= mksqlite('SELECT SUM(position) as sm FROM tbl_portfolios WHERE accountName="acct1"') 


  1. Thanks for sharing this nice example script!

  2. this is good. i also work on this thing. please visit my blogs.

  3. Excellent example. Thank you!
    For novices it should be mentioned that the 19th line "mksqlite('DROP TABLE tbl_portfolios');" must be commented the very firs time.

    1. or you may use
      mksqlite('DROP TABLE IF EXISTS tbl_portfolios');

  4. mksqlite has been moved to sourceforge:

    Cheers, A.Martin

  5. I'd also want to point out the availability of a No-SQL database toolbox that would reduce the "lines of code" significantly. It works for Sqlite and Mysql (and other systems).