import data from Excel

조회 수: 3 (최근 30일)
tr206
tr206 2015년 3월 19일
댓글: dpb 2015년 3월 21일
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.
  댓글 수: 8
tr206
tr206 2015년 3월 21일
편집: dpb 2015년 3월 21일
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
dpb 2015년 3월 21일
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.

댓글을 달려면 로그인하십시오.

답변 (2개)

Konstantinos Sofos
Konstantinos Sofos 2015년 3월 19일
2)see how can you use xlsread
3)Regarding the dates...Import tool has an option to convert dates to serial number

Nabeel
Nabeel 2015년 3월 20일
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

카테고리

Help CenterFile Exchange에서 Data Import from MATLAB에 대해 자세히 알아보기

제품

Community Treasure Hunt

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

Start Hunting!

Translated by