import data from Excel

4 次查看（过去 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 个评论
显示 6更早的评论
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
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
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 Center 和 File 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