import data from Excel

Hi guys,

I want to import historical stock returns in an Excel sheet into matlab. I have used the import tool in Matlab 2013 but it does not work. I have tried all types for the variables (i.e. matrix, dataset, cell array etc.). When I use the variable type dataset I get the message

Error using diff Function 'diff' is not supported for class 'dataset'.

The datafile has the format

yyyy mm dd yyyymmdd vwretd ewretd

where yyyy: four digit year (e.g., 1980) mm: two digit month (e.g., 01) dd: two digit day (e.g., 04) yyyymmdd: eight digit date (e.g., 19800104) vwretd: value-weighted return including all distributions ewretd: equally-weighted return including all distributions

The person who used this format to import the data told me that the file is a Matlab data file. So, how can I import the data from Excel to run my analysis such as a regression?

How can I create a Matlab data file?

Another problem is that the values for mm and dd are not in two digits e.g. 04 or 05 for the fourth and fifth day in a month but 4 or 5. I want Matlab to express these day as 04 or 05 but how can I do that?

Thanks for your help.

[dpb]

Well, much more is undefined in your question than is, unfortunately.

Is the data actually in an Excel worksheet? If so, then using xlsread should work essentially transparently and leave you with a numeric array for the fields that are numeric and a cell array for that which isn't. Not knowing how the sheet is set up we can't tell a priori.

OTOH, the phrase "is a Matlab data file" is quite nebulous; it it has the meaning of a Matlab-specific .mat file, then one would use load and the variables will show up automagically. Again, we can't tell specifically from the description.

Let's worry about output presentation once we get somewhere with figuring out the input. Can you attach a file so we can actually see what is going on? Use the paper clip button to do so...

[tr206]

Thank you very much. Attached, please find the file I want to import. I have the code I want to apply to these data and I use load to show up the variables but how can I create such a Matlab-specific file?

[dpb]

Try the attach again...didn't take.

[tr206]

please see the attachment above

[dpb]

data=xlsread('stkmkt_ret_data.xlsx');

will return all the numeric values as an array and you can then work on them to your heart's content. What's the problem?

[Sean de Wolski]

What version of MATLAB do you have? Are you trying to import as a dataset (as the error message suggests)?

[tr206]

I use Matlab R 2013a. the xlsread command works but I do not get the output I want. Instead of getting the returns and the corresponding date I get an output which looks as follows:

    0.0002    0.0000    0.0000    1.9861    0.0000
    0.0002    0.0000    0.0000    1.9861   -0.0000
    0.0002    0.0000    0.0000    1.9861   -0.0000
    0.0002    0.0000    0.0000    1.9861   -0.0000
    0.0002    0.0000    0.0000    1.9861   -0.0000
    0.0002    0.0000    0.0000    1.9861   -0.0000
I want the format as shown in the attached Excel-file.

How can I create a Matlab data file? I want to use the command load to the data.

[dpb]

You have a Matlab array, the data are simply scaled for display at the command prompt; NB: there's a scale factor printed at the top of the array.

Try

data(1:10,5)

or similar and see. Internally the storage is full double precision. The prime difficulty in view the data as it is stored is that the date stored as yyyymmdd is a large numeric value in comparison to the rest which is causing the scaling to be make the significant digits of the smaller values slide off the radar. I don't see that having that in that form will be of much use in any analyses as you've got the date info in more useful form as y,m,d in the first three columns anyway so I'd probably just delete the fourth column and keep only the others and for convenience move the time info into one array and the returns to a corresponding vector...

ymd=data(:,1:3);  % yr, mo, day array
retn=data(:,5);   % returns

and use those for more convenient naming. I don't know what you intend to do with the data; there is a financial toolbox but I've never had it so don't know how it handles its time series, specifically.

Answer 1

[Konstantinos Sofos]

1) Have a look here Importing Spreadsheets into MATLAB

2)see how can you use xlsread

3)Regarding the dates...Import tool has an option to convert dates to serial number

Answer 2

[Nabeel]

i am using Matlab2013a and recently used this command to import data form excel input=xlsread('stkmkt_ret_data.xlsx') if you want to input data from specific column than you can use this command yyyy=xlsread('stkmkt_ret_data.xlsx','A:A') to export data into excel you can you this xlswrite('output.xlsx',AD1,'AS2:AS2'); % AD1 is a variable which you want to export