MATLAB Answers

Method of extracting data from CSV

74 views (last 30 days)
Dear All, I have a very large CSV file with 5 columns which contains some historical financial prices.
Column 1 is our date starting from 1/3/2007 all the way till till mid 2011 in the form of mm/dd/yyyy.
Column 2 is our time of out trades in the form of :
35:45.0 35:45.0 35:46.0 35:46.0 35:46.0 (as seen in excel)
However when I change the data type of this column in excel from CUSTOM to TIME I get something better which I need:
13:07:23 13:07:24 13:07:24 13:07:25 13:07:25
Column 3 is our respective prices
Column 4 is our symbol (stays the same)
Column 5 is the number of trades.
So my question is I have all of this information...
I want to put the all the columns in one matrix in MATLAB (apart from 4). But I DONT want it to be a string matrix as I want to do some operations with the data.
Hint: May need to convert column 1 and 2 in to number format?

  0 Comments

Sign in to comment.

Accepted Answer

Walter Roberson
Walter Roberson on 12 Apr 2013
fid = fopen('YourFile.csv', 'rt');
datacell = textscan(fid, '%s%s%f%*s%f', 'Delimiter', ',');
fclose(fid);
dates = datenum(datacell{1}, 'mm/dd/yyyy');
outtrades = datenum(datacell{2}, 'MM:SS:FFF');
prices = datacell{3};
ntrades = datacell{5};
datamatrix = horzcat(dates, outtrades, prices, ntrades);
Question: is there no hour in the data?

  6 Comments

Show 3 older comments
per isakson
per isakson on 12 Apr 2013
Proposal:
  • open your csv-file in an editor, e.g. Matlabs editor or Notepad
  • copy&paste a few lines to the question
Mate 2u
Mate 2u on 12 Apr 2013
CSV does not fully open on matlabs editor due to the limit of 1 million rows.
Mate 2u
Mate 2u on 12 Apr 2013
Actually this suffices.

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!