Clear Filters
Clear Filters

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

4 views (last 30 days)
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

Accepted Answer

Walter Roberson
Walter Roberson on 19 Nov 2011
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
  4 Comments
Walter Roberson
Walter Roberson on 20 Nov 2011
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
Scragmore on 22 Nov 2011
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

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!