Thread Subject:

Have text file of stock data, help manipulating and saving as csv

Howdy. I have a text file of stock data. The text file looks like this: http://ftp.hlmtrading.com/incoming/Prime2011-01-25_115947.jpg

You can see it has several columns of Date,Time,Open,High,Low,Close,Volume, Number_of_Trades, Bid_volume and Ask Volume.

What I am wanting to do is take this data, strip off a few of the columns and modify the "Date" column.

The finished product would be a csv file that would look like this:
http://ftp.hlmtrading.com/incoming/Prime2011-01-25_120205.jpg

So the only columns I wish to retain are the Date,Open,High,Low and close columns.

What I would then like to do is remove the current date column and add a 'synthetic' type of date vector. And that would be to replace the current date vector, with one that puts the current date at the bottom/end of that vector, and each date above it would be one day less(the prior day).... BUT it needs to plot dates that occurred on the weekdays, skipping the dates that occurred on the weekends. So I'm thinking I will be using something like, plot only if 'isweekday.'

Mind you this date vector has no impact on the other data as it is entirely synthetic and just used for another script I have written which doesn't like plotting weekends. I only wish to make sure the dates in this column are the dates of actual weekdays.

Post a couple of lines with the headers of your initial file (I personally don't want to lose time creating myself example input dataor even worse copying your snap since you can just copy paste it here), and the desired output. Then we'll show you the steps to get there.

Oleg

 Post a couple of lines with the headers of your initial file (I personally don't want to lose time creating myself example input dataor even worse copying your snap since you can just copy paste it here), and the desired output. Then we'll show you the steps to get there.

No problems at all. I was wanting to attach the text file but didn't even think of just pasting some lines here. Here are a few lines with the header of the original.

Date, Time, Open, High, Low, Close, Volume, Number_of_Trades, Bid_volume, Ask_volume
12/27/2010, 00:00:00, 1.31274998, 1.31344497, 1.31260502, 1.31309998, 542, 542, 261, 281
12/27/2010, 00:15:00, 1.31309497, 1.3139801, 1.31300497, 1.3139801, 431, 431, 182, 246
12/27/2010, 00:30:00, 1.31398511, 1.31487, 1.31383991, 1.31487, 1225, 1225, 577, 640
12/27/2010, 00:45:00, 1.31487501, 1.31612492, 1.31478, 1.31584001, 1319, 1319, 614, 704
12/27/2010, 01:00:00, 1.315835, 1.31704497, 1.31570005, 1.315835, 1294, 1294, 633, 661
12/27/2010, 01:15:00, 1.31586003, 1.31644499, 1.31586003, 1.31594992, 745, 745, 370, 375
12/27/2010, 01:30:00, 1.31594491, 1.31604004, 1.31503999, 1.31507993, 1037, 1037, 556, 481
12/27/2010, 01:45:00, 1.31507492, 1.31561494, 1.3150301, 1.31544495, 901, 901, 439, 462
12/27/2010, 02:00:00, 1.31543493, 1.31595993, 1.31486011, 1.31540489, 1457, 1457, 694, 763
12/27/2010, 02:15:00, 1.3154099, 1.31557012, 1.313905, 1.31442499, 1779, 1779, 937, 842
12/27/2010, 02:30:00, 1.31441998, 1.31492996, 1.31423497, 1.31447506, 1222, 1222, 603, 619
12/27/2010, 02:45:00, 1.31448007, 1.31496501, 1.31423998, 1.31496501, 1188, 1188, 569, 619
12/27/2010, 03:00:00, 1.31496, 1.31544995, 1.31480002, 1.31511497, 1029, 1029, 520, 509

Here is an example of the output copied from the csv file. Here you can see the date at the bottom is always the current date, and decrement by 1 day, excluding Saturday and Sunday.


Date Open High Low Close
01/10/11 1.36726499 1.36731005 1.36278009 1.3631351
01/11/11 1.36313009 1.36315989 1.36064005 1.3613801
01/12/11 1.36138511 1.36271 1.36104989 1.36207008
01/13/11 1.36207509 1.36436009 1.36198997 1.36427498
01/14/11 1.36427999 1.36561501 1.36394501 1.36525512
01/17/11 1.36526 1.36576509 1.36507511 1.36566496
01/18/11 1.36565995 1.36568499 1.36405492 1.36438489
01/19/11 1.36438 1.36493492 1.3640449 1.36429501
01/20/11 1.36431003 1.36495495 1.36327505 1.36367512
01/21/11 1.36368001 1.36434507 1.36346507 1.36381507
01/24/11 1.36382008 1.36535501 1.36363506 1.36535501
01/25/11 1.36536002 1.36601496 1.3650949 1.36572504

"Forrest" wrote in message <ihn7a3$i49$1@fred.mathworks.com>...
> Post a couple of lines with the headers of your initial file (I personally don't want to lose time creating myself example input dataor even worse copying your snap since you can just copy paste it here), and the desired output. Then we'll show you the steps to get there.
>
 No problems at all. I was wanting to attach the text file but didn't even think of just pasting some lines here. Here are a few lines with the header of the original.

Date, Time, Open, High, Low, Close, Volume, Number_of_Trades, Bid_volume, Ask_volume
12/27/2010, 00:00:00, 1.31274998, 1.31344497, 1.31260502, 1.31309998, 542, 542, 261, 281
12/27/2010, 00:15:00, 1.31309497, 1.3139801, 1.31300497, 1.3139801, 431, 431, 182, 246
12/27/2010, 00:30:00, 1.31398511, 1.31487, 1.31383991, 1.31487, 1225, 1225, 577, 640
12/27/2010, 00:45:00, 1.31487501, 1.31612492, 1.31478, 1.31584001, 1319, 1319, 614, 704
12/27/2010, 01:00:00, 1.315835, 1.31704497, 1.31570005, 1.315835, 1294, 1294, 633, 661
12/27/2010, 01:15:00, 1.31586003, 1.31644499, 1.31586003, 1.31594992, 745, 745, 370, 375
12/27/2010, 01:30:00, 1.31594491, 1.31604004, 1.31503999, 1.31507993, 1037, 1037, 556, 481
12/27/2010, 01:45:00, 1.31507492, 1.31561494, 1.3150301, 1.31544495, 901, 901, 439, 462
12/27/2010, 02:00:00, 1.31543493, 1.31595993, 1.31486011, 1.31540489, 1457, 1457, 694, 763
12/27/2010, 02:15:00, 1.3154099, 1.31557012, 1.313905, 1.31442499, 1779, 1779, 937, 842
12/27/2010, 02:30:00, 1.31441998, 1.31492996, 1.31423497, 1.31447506, 1222, 1222, 603, 619
12/27/2010, 02:45:00, 1.31448007, 1.31496501, 1.31423998, 1.31496501, 1188, 1188, 569, 619
12/27/2010, 03:00:00, 1.31496, 1.31544995, 1.31480002, 1.31511497, 1029, 1029, 520, 509
-----------------------------------------------------------------------------------------------------------------------------------------

here is the copied output of the csv file
Date Open High Low Close
01/13/11 1.31274998 1.31344497 1.31260502 1.31309998
01/14/11 1.31309497 1.3139801 1.31300497 1.3139801
01/17/11 1.31398511 1.31487 1.31383991 1.31487
01/18/11 1.31487501 1.31612492 1.31478 1.31584001
01/19/11 1.315835 1.31704497 1.31570005 1.315835
01/20/11 1.31586003 1.31644499 1.31586003 1.31594992
01/21/11 1.31594491 1.31604004 1.31503999 1.31507993
01/24/11 1.31507492 1.31561494 1.3150301 1.31544495
01/25/11 1.31543493 1.31595993 1.31486011 1.31540489

% Read in data skipping first column and discarding the rest after OHLC
fid = fopen('C:\Users\Oleg\Desktop\load.txt');
data = textscan(fid, '%*s %*s %f %f %f %f %*[^\n]','Headerlines',1,'Delimiter',',');
fid = fclose(fid);

% Reorganize
data = [cellstr(datestr(now-size(data{1},1)+1:now,'mm/dd/yy')) num2cell([data{:}])].';

% Write data
fid = fopen('C:\Users\Oleg\Desktop\load.csv','w');
fprintf(fid, 'Date, Open, High, Low, Close\n');
fprintf(fid, '%s, %f, %f, %f, %f\n',data{:});
fid = fclose(fid);

Oleg

"Oleg Komarov" <oleg.komarovRemove.this@hotmail.it> wrote in message <ihn9mv$obd$1@fred.mathworks.com>...
> % Read in data skipping first column and discarding the rest after OHLC
> fid = fopen('C:\Users\Oleg\Desktop\load.txt');
> data = textscan(fid, '%*s %*s %f %f %f %f %*[^\n]','Headerlines',1,'Delimiter',',');
> fid = fclose(fid);
>
> % Reorganize
> data = [cellstr(datestr(now-size(data{1},1)+1:now,'mm/dd/yy')) num2cell([data{:}])].';
>
> % Write data
> fid = fopen('C:\Users\Oleg\Desktop\load.csv','w');
> fprintf(fid, 'Date, Open, High, Low, Close\n');
> fprintf(fid, '%s, %f, %f, %f, %f\n',data{:});
> fid = fclose(fid);
>
> Oleg

Wow!! Thanks a ton Oleg, this already does what I was doing and saves a ton of time.

Do you know what it will take to make that date vector ONLY include dates that fall BETWEEN Monday and Friday, by excluding the weekdays?

In the example above, in the output I posted shows the days in sequential order, but skipping the dates that fall on a Sat or Sun.

Looking into the 'isbusday.'

"Forrest" wrote in message <ihnemb$f9i$1@fred.mathworks.com>...
> Looking into the 'isbusday.'

Use this for the reorganize part:

% Reorganize
Dates = now-size(data{1},1)+1:now;
idx = ~ismembc(weekday(Dates),[7,1]);
data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell([data{idx}])].';


Oleg

I seem to be getting an error.

">> ??? Index exceeds matrix dimensions.
??? ??? Index exceeds matrix dimensions.
     |
Error: Unexpected MATLAB operator."

"Forrest" wrote in message <ihnk8e$1ho$1@fred.mathworks.com>...
> I seem to be getting an error.
>
> ">> ??? Index exceeds matrix dimensions.
> ??? ??? Index exceeds matrix dimensions.
> |
> Error: Unexpected MATLAB operator."

Is that the full error message?
Also which version of matlab are you using?

Oleg

Here is the full error message:

" ??? Index exceeds matrix dimensions.

Error in ==> EurUsdStripper at 13
data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell([data{idx}])].'; "

The version I'm using is: 7.10.0.499 (R2010a)

"Forrest" wrote in message <ihnmdv$mh6$1@fred.mathworks.com>...
> Here is the full error message:
>
> " ??? Index exceeds matrix dimensions.
>
> Error in ==> EurUsdStripper at 13
> data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell([data{idx}])].'; "
>
> The version I'm using is: 7.10.0.499 (R2010a)

I didn't put " at the end of that line of code, just erase it.

Oleg

It's not in the code, I used the " to show that that was the error message. What I mean is that in the code the quatoation wasn't there, I used the " to wrap around the error message.

Forrest wrote:
> Here is the full error message:
>
> " ??? Index exceeds matrix dimensions.
>
> Error in ==> EurUsdStripper at 13
> data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell([data{idx}])].'; "
...

That would imply (assuming the line is Line 13 in the EurUsdStripper
function) that the indices in idx aren't consonant w/ the dimensions of
either Dates and/or data.

--

So what I am saying is this picture here. I am highlighting the areas I put the quotations on. They aren't actually in the code.

http://ftp.hlmtrading.com/incoming/Prime2011-01-26_092248.jpg

"Forrest" wrote in message <ihpeak$ioh$1@fred.mathworks.com>...
> So what I am saying is this picture here. I am highlighting the areas I put the quotations on. They aren't actually in the code.
>
> http://ftp.hlmtrading.com/incoming/Prime2011-01-26_092248.jpg

Dates probably don't match the data length...this is due to the fact that in the snippet I gave you they are generated independently, and of course based on assumptions of "normal" behaviour of the file...therefore I can't really help here. Try to look at the size of the variable and post it here.

Oleg

Oleg,

Take a look at this picture of the workspace after running it:
http://ftp.hlmtrading.com/incoming/Prime2011-01-26_133918.jpg

Forrest wrote:
> Oleg,
>
> Take a look at this picture of the workspace after running it:
> http://ftp.hlmtrading.com/incoming/Prime2011-01-26_133918.jpg

data is 1x4 cell, idx is 1x2167.

Your mission, should you choose to accept it, is to find out why and fix
the size of the data array to match the indices.

I suppose there could be a combination of items in the 4 cells that add
to 2167, but no clue here about that.

But, clearly the problem Matlab is complaining about is that you're
trying to access non-existent array elements.

--

Let me know if this link works to a download of the original text file:
http://uploading.com/files/c7f8b5de/EurUsd-BarData.txt/

dpb <none@non.net> wrote in message <ihpuue$quq$1@news.eternal-september.org>...
>
> data is 1x4 cell, idx is 1x2167.
>
> Your mission, should you choose to accept it, is to find out why and fix
> the size of the data array to match the indices.
>
> I suppose there could be a combination of items in the 4 cells that add
> to 2167, but no clue here about that.
>
> But, clearly the problem Matlab is complaining about is that you're
> trying to access non-existent array elements.
>
> --
I'm stepping through this trying to understand it. Looks like in that variable "Dates" is a 1x4 as you mentioned, in each of those 4 contains the 2167 values.

So Dates(1:) contains 2167 values for the "Open." Dates(2:) contains 2167 values for the "High." and so on for the "Low" and "Close" columns.

"Forrest" wrote in message <ihq0nb$huk$1@fred.mathworks.com>...
> dpb <none@non.net> wrote in message <ihpuue$quq$1@news.eternal-september.org>...
> >
> > data is 1x4 cell, idx is 1x2167.
> >
> > Your mission, should you choose to accept it, is to find out why and fix
> > the size of the data array to match the indices.
> >
> > I suppose there could be a combination of items in the 4 cells that add
> > to 2167, but no clue here about that.
> >
> > But, clearly the problem Matlab is complaining about is that you're
> > trying to access non-existent array elements.
> >
> > --
> I'm stepping through this trying to understand it. Looks like in that variable "Dates" is a 1x4 as you mentioned, in each of those 4 contains the 2167 values.
>
> So Dates(1:) contains 2167 values for the "Open." Dates(2:) contains 2167 values for the "High." and so on for the "Low" and "Close" columns.

My bad, I tried to compress an action I couldn't really do, so:

data = [data{:}];
data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell(data(idx,:))].';

Try with these two lines of code...

Oleg

"Oleg Komarov" <oleg.komarovRemove.this@hotmail.it> wrote in message <ihq2fj$bs4$1@fred.mathworks.com>...
> Try with these two lines of code...
>
> Oleg

Running this, it doesn't seem to take out the weekends. So it still has the same output as the other lines.

The total script I have is this:


% Read in data skipping first column and discarding the rest after OHLC
fid = fopen('C:\SierraChart\data\EurUsd-BarData.txt');
data = textscan(fid, '%*s %*s %f %f %f %f %*[^\n]','Headerlines',1,'Delimiter',',');
fid = fclose(fid);

% Reorganize
Dates = now-size(data{1},1)+1:now;
idx = ~ismembc(weekday(Dates),[7,1]);
data = [data{:}];
data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell(data(idx,:))].';

% Write data
fid = fopen('C:\SierraChart\data\EurUsd-BarData.csv','w');
fprintf(fid, 'Date, Open, High, Low, Close\n');
fprintf(fid, '%s, %f, %f, %f, %f\n',data{:});
fid = fclose(fid);

> idx = ~ismembc(weekday(Dates),[7,1]);

idx = ~ismember(weekday(Dates),[7,1]);

Oleg

"Oleg Komarov" <oleg.komarovRemove.this@hotmail.it> wrote in message <ihqqef$eb$1@fred.mathworks.com>...
> > idx = ~ismembc(weekday(Dates),[7,1]);
>
> idx = ~ismember(weekday(Dates),[7,1]);
>
> Oleg

Yep that was it!!! It is working exactly the way i want it to now and will save me a lot of time.

Much appreciated, thanks for writing this. I'm still deciphering some of it :)

One small problem.......

While going over the script, I noticed that the weekends are pulled out AFTER the Date column is joined to the OHLC values. So this means that some data would be missing, as that data that falls on a weekend is removed along with the weekend date.

I think when I explained it I probably didn't explain that part properly.

I checked this and the data is a bit off. What needs to happen is to generate the date column after the weekends are stripped from the date array.

I am attaching a picture showing what I mean. Starting from the bottom, you can see the bottom 4 are weekdays as it goes from 1/27/2011 up to 1/24/2011. Then you can see there is a jump from 1/24/2011 to 1/21/2011 as this is a weekend. You can see how the data of the OHLC columns is skipped by a few rows, where some data is missing in the output.

I think the picture explains it, I've drawn a red box around the two days missing:
http://ftp.hlmtrading.com/incoming/Prime2011-01-27_121140.jpg

Trying to create a loop to create the Dates array. My logic is that first you get the size of the array, which tells you how many dates you need to populate this array.

Next I'm thinking you would start at the END of the array, i.e. the current date.

Next you would create a loop that would run for the SIZE of that array. The loop would start at the ending date, and subtract 1 day to get the prior date ONLY IF that day is NOT Sunday. ELSE, Subtract 3 days. Execute this loop as many times as your SIZE you found earlier.

Here is my code which doesn't work btw as I don't think I can just subtract 1 from a date. I've commented out the other portion as I'm just trying to create the date array atm.

% Read in data skipping first column and discarding the rest after OHLC
fid = fopen('C:\SierraChart\data\EurUsd-BarData.txt');
data = textscan(fid, '%*s %*s %f %f %f %f %*[^\n]','Headerlines',1,'Delimiter',',');
fid = fclose(fid);

% Reorganize
Counter = size(data{1},1); %get size of array
Dates1(Counter) = now; %initialize starting point which is current day(end of array)
CountBack = 0;

for j=Counter-1:-1:1
  CountBack = Countback+1;
   if ~ismember(weekday(Dates1),[1]);
       Dates1(:j) = now - CountBack;
   else
       Dates1(:j) = now - CountBack-2;
   end
    
end

%Dates = now-size(data{1},1)+1:now;
%idx = ~ismember(weekday(Dates),[7,1]);
%data = [data{:}];
%data = [cellstr(datestr(Dates(idx),'mm/dd/yy')) num2cell(data(idx,:))].';

% Write data
fid = fopen('C:\SierraChart\data\EurUsd-BarData.csv','w');
fprintf(fid, 'Date, Open, High, Low, Close\n');
fprintf(fid, '%s, %f, %f, %f, %f\n',data{:});
fid = fclose(fid);

> % Reorganize
> Counter = size(data{1},1); %get size of array
> Dates1(Counter) = now; %initialize starting point which is current day(end of array)
> CountBack = 0;
>
> for j=Counter-1:-1:1
> CountBack = Countback+1;
> if ~ismember(weekday(Dates1),[1]);
> Dates1(:j) = now - CountBack;
> else
> Dates1(:j) = now - CountBack-2;
> end
>
> end

Try this one

Dates = size(data{1},1);
Dates(end) = busdate(now,-1);
for d = numel(Dates):-1:2
% If monday
    if weekday(Dates(d)) == 2
% If sunday
        Dates(d-1) = Dates(d)-3;
% Else
    elseif weekday(Dates(d)) == 1
        Dates(d-1) = Dates(d)-2;
    else
        Dates(d-1) = Dates(d)-1;
    end
end

Oleg

It looks like this evaluates everything to the same date. See the attached pic with the output, I just converted to dates and all the same date showed up.

http://ftp.hlmtrading.com/incoming/Prime2011-01-28_035144.jpg

"Forrest" wrote in message <ihu3m0$fvk$1@fred.mathworks.com>...
> It looks like this evaluates everything to the same date. See the attached pic with the output, I just converted to dates and all the same date showed up.
>
> http://ftp.hlmtrading.com/incoming/Prime2011-01-28_035144.jpg

After creating the Dates vector which is in serial/numeric format you have to convert it to string format with (see here why matlab and excel serial numbering doesn't match http://www.mathworks.com/help/toolbox/finance/m2xdate.html):

datestr(Dates,'mm/dd/yy')

Oleg

"Oleg Komarov" <oleg.komarovRemove.this@hotmail.it> wrote in message <ihuna3$oq3$1@fred.mathworks.com>...
> "Forrest" wrote in message <ihu3m0$fvk$1@fred.mathworks.com>...
> > It looks like this evaluates everything to the same date. See the attached pic with the output, I just converted to dates and all the same date showed up.
> >
> > http://ftp.hlmtrading.com/incoming/Prime2011-01-28_035144.jpg
>
> After creating the Dates vector which is in serial/numeric format you have to convert it to string format with (see here why matlab and excel serial numbering doesn't match http://www.mathworks.com/help/toolbox/finance/m2xdate.html):
>
> datestr(Dates,'mm/dd/yy')
>
> Oleg

Ah ok gotcha.