Visitors Now:
Total Visits:
Total Stories:
Profile image
By Quantum Blog (Reporter)
Contributor profile | More stories
Story Views

Now:
Last Hour:
Last 24 Hours:
Total:

Add database functionality to Matlab with SQLite.

Tuesday, October 25, 2016 6:24
% of readers think this story is Fact. Add your two cents.

(Before It's News)

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: http://mksqlite.berlios.de/mksqlite_eng.html
(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;
clc;
mksqlite('open','test.db');

tables = mksqlite('show tables');
disp(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)';
mksqlite(sql);

%% 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!
tic
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));
else
fprintf('Updating symbol n');
mksqlite(sprintf('UPDATE tbl_portfolios SET position=%i WHERE id=%i',position,res.id));
end

end
%mksqlite('PRAGMA synchronous=NORMAL');
mksqlite('END');

toc

%% now pull the data from database

fprintf('nGetting data from databasen');

res = mksqlite('SELECT * FROM tbl_portfolios ORDER BY accountName ASC');
fprintf('AccounttSymboltpositionn-----------------------n');
for i=1:length(res)
fprintf('%st%st%in', res(i).accountName, res(i).symbol,res(i).position);
end

%% 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"')

Report abuse

Comments

Your Comments
Question   Razz  Sad   Evil  Exclaim  Smile  Redface  Biggrin  Surprised  Eek   Confused   Cool  LOL   Mad   Twisted  Rolleyes   Wink  Idea  Arrow  Neutral  Cry   Mr. Green

Top Stories
Recent Stories

Register

Newsletter

Email this story
Email this story

If you really want to ban this commenter, please write down the reason:

If you really want to disable all recommended stories, click on OK button. After that, you will be redirect to your options page.