No BSD License  

image thumbnail

DatabaseCell2Mat

by

 

20 Aug 2003 (Updated )

Converts the cell matrix that the Database Toolbox provides into a numeric matrix and a structure.

[mdata,s]=DatabaseCell2Mat(GSData,attrval)
function [mdata,s]=DatabaseCell2Mat(GSData,attrval)
% DATABASECELL2MAT converts the cell matrix that
% the Database Toolbox provides into a numeric
% matrix and a structure.  The fieldnames of the
% structure will be the same as the column names
% of the tables.
%
% It converts date/time types into MATLAB julians and
% bit types into 1s and 0s.
%
% Without this utility, dates would be in a string
% format not compatible with datenum and the bit
% types would be "true" or "false".
%
% CAVEATS:
% Times are ignored
% Strings are not handled.
%
% INPUTS:
% Cellstring matrix
% Table attributes
%
% USAGE:
% c=database('mydb','','');
% query=['SELECT * FROM mytable;'];
% curs=exec(c,query);
% curs=fetch(curs);
% [mat,s]=DatabaseCell2Mat(curs.Data,attr(curs));
% close(c);
% >> curs.data(1:4,:)
%     [4.2][1x21 char][-13.3][ 1.8][ 6.4][-7.9][-24.7][1x21 char] 'false'
%     [5.5][1x21 char][ -4.7][ 6.8][11.7][-1.8][-17.2][1x21 char] 'false'
%     [  4][1x21 char][ -7.3][ 2.3][ 7.2][-3.7][-17.1][1x21 char] 'false'
%     [5.7][1x21 char][ -6.6][-0.3][ 9.4][-1.6][  -16][1x21 char] 'false'
% >> curs.data(1:4,2)
%     '2003-03-31 00:00:00.0'
%     '2003-03-31 00:00:00.0'
%     '2003-04-30 00:00:00.0'
%     '2003-04-30 00:00:00.0'
% >> mat(1:4,:)
%   1.0e+005 *
%     0.0000 7.3167 -0.0001  0.0000 0.0001 -0.0001 -0.0002 7.3164 0
%     0.0001 7.3167 -0.0000  0.0001 0.0001 -0.0000 -0.0002 7.3164 0
%     0.0000 7.3170 -0.0001  0.0000 0.0001 -0.0000 -0.0002 7.3164 0
%     0.0001 7.3170 -0.0001 -0.0000 0.0001 -0.0000 -0.0002 7.3164 0
% >> s
%         Coupon: [13215x1 double]
%       Maturity: [13215x1 double]
%        Axx_Spd: [13215x1 double]
%         AX_COD: [13215x1 double]
%     AX_100dMax: [13215x1 double]
%     AX_100dAvg: [13215x1 double]
%     AX_100dMin: [13215x1 double]
%        AX_Date: [13215x1 double]
%        Current: [13215x1 double]
%
% It's not fancy, but it works.

% Michael Robbins
% robbins@bloomberg.net
% michael.robbins@us.cibc.com
% thanks to us (us@neurol.unizh.ch)

temp=attrval;
GSDCN={temp.fieldName};
GSDTN={temp.typeName};

% INITIALIZE
[L,W]=size(GSData);
mdata=zeros(L,W);
datetimei=strmatch('DATETIME',GSDTN);
biti=strmatch('BIT',GSDTN);
numericali=setdiff([1:W],[datetimei.' biti.']);

% CHANGE DATES
datetimei=datetimei;
gsd=char(GSData(:,datetimei));
mdata(:,datetimei)=reshape( ...
    datenum(reshape(sscanf(gsd(:,1:10).', ...
   '%4d-%2d-%2d'),3,[]).'), ...
   L,length(datetimei));

% CHANGE BOOLEANS
mdata(strmatch('true',GSData(:,biti)),biti)=1;

% CELL->MAT
i=[1 3:7];
mdata(:,numericali)=reshape( ...
    [GSData{:,numericali}], ...
    length([GSData{:,1}]),length(numericali));

% STRUCTUREIZE
s=[];
for i=1:length(GSDCN)
    s=setfield(s,GSDCN{i},mdata(:,i));
end;

Contact us