Is there a more effitiant way than 'datenum(n​um2str(FTS​tempIn(:,1​:1))'

Hi

Profiler is telling me I am spending over 60% of my time running this part of my function, a simple import of data. What I am converting is a number field in MS access from 20111119 to Matlab datenum, I wish to add a second conversion to deal with the time 143000 (14:30:00 HHMMSS). I am currently running this on 150k lines of data. Is there a more efficient way of dealing with these two conversions.

function InstOut = LoadForexData(InstIn)
%%%%Import market data from Quaccess via ticker PK key
% 1 Ticker index ID in need to convert No to string so SQL can Run
% 2 Concatanate SQL query as variable so can be called
% 3 Config DB
% 4 set inport as cellarray
% 5 execute at cursor connnectinon to DB and run SQL
% 6 get data at cursor
% 7 load data at curor into temp varible for further analysis
% 8 load data into second temp variable performing cell to matrix on all
%   numerical values and date to number on date format.
% 9 send transformed data to output
% 10 ScoobyDoo ending, raw data to output.
%1
Tik = num2str(InstIn);
%2
ConCatQuery = ['SELECT DISTINCT yyyymmdd,Time,Open,High,Low,Close FROM Forex_GBP_USD WHERE yyyymmdd > (',Tik,')'];
%3
conn = database('Dougy','','');
%4
setdbprefs('DataReturnFormat', 'numeric');
%5
curs = exec(conn, ConCatQuery);
%6
curs = fetch(curs);
%7
FTStempIn = curs.Data;
%8
FTStempOut = [datenum(num2str(FTStempIn(:,1:1)), 'yyyymmdd')  FTStempIn(:,3:6)];
%9
InstOut = FTStempOut;
%10
%InstOut = curs.Data;

Thanks,

AD

Answer 1

[Walter Roberson]

t = FTStempIn(:,1);
hms = repmat([14 30 0],size(t,1),1]);
FTStempOut = datenum([fix(t/10000), fix(mod(t,10000)/100), mod(t,100), hms]);  %1000 corrected to 10000

[Scragmore]

Walter,

Thanks, would it be possible to provide a brief explanation of what this does and why it more efficient. I will check out the help files for the functions to understand their use.

On a different note. Watched Buckaroo Banzai, off the wall and wicked. Thanks for the quote.

Ad

[Scragmore]

Not sure I made it clear or if it effects your response, both my date and time increment through my whole data set.

[Walter Roberson]

Note: I corrected the answer to use 10000 instead of 1000

datenum(num2str(FTStempIn(:,1:1)), 'yyyymmdd')

requires nine divisions by 10 per input number in order to format the number properly, with num2str() having the overhead of dispatching to sprintf() for each number, and having the overhead of accumulating the numbers in a cell array and right justifying the strings at the end when it converts the cell array to a char array (because that's how num2str() handles column vectors of data). And once those divisions are done and the input is handed over to datestr, datestr has to parse each row individually, making sure that it has the right size, dealing with blanks, and figuring out how to break of the strings according to the 'yyyymmdd' format, and converting the segmented strings in to numbers, that get pushed in to a datevec array that then gets to be converted to serial day numbers. Oh, and your version doesn't do the 14:30 either.

The version I suggest above, on the other hand, does four arithmetic calculations per input, does no writing of strings and no parsing and no cross-checking of strings, and directly constructs the datevec array (that includes the 14:30), that is then converted to serial day numbers. This should be a _lot_ less calculation and a lot fewer calls.

fix(t/10000) divides t by 10000 and takes the integer part, which is the same thing as dropping the last 4 digits of the decimal representation, thus extracting the year

fix(mod(t,10000)/100) extracts the last 4 digits of the decimal representation, and then divides that by 100 and takes the integer part, and thus effectively extracts the 5th and 6th digit of the decimal representation, the two digit month.

mod(t,100) extracts the last two digits of the decimal representation, leaving the two digit day.

The bit with the hms is to add the 14:30 (and 0 seconds) to each numeric date vector. If your time is not constant (which I what I had understood when you said 14:30 specifically), then at that location you should change the hms to code to extract the hours minutes and seconds from where-ever you have them stored.

[Scragmore]

Thanks for the explanation I appreciate it. Once I got the logic you used for bypassing num2str I was able to apply to my time cell and include it into the datenum function. Has cut time of function in half.

As an Excell user I was thinking along these lines but was unable to find a simple alternative to excel's left, right and mid functions. I was stuck in string thinking and didn't think math. Thanks

Regards,

AD