Reading a Datetime Column as Numbers Instead of Actual Dates in MATLAB

9 次查看（过去 30 天）
Teoman
Teoman 2023-9-26
评论： the cyclist 2023-9-26
I am working with financial data stored in an Excel file, and I'm encountering an issue when reading the datetime column. The data in the datetime column is being read as numbers, similar to this:
Data for AAPL Historical Data.xlsx:
| Date | Price | Open | High | Low | Vol. | Change % |
|:------------:|:---------:|:---------:|:---------:|:---------:|:--------:|:---------:|
| 15-Jan-2019 | 152.8000 | 150.2700 | 152.8900 | 150.0600 | 16.82M | 0.0187 |
| 14-Jan-2019 | 150 | 150.8500 | 151.2400 | 149.2200 | 32.44M | -0.0150 |
| ... | ... | ... | ... | ... | ... | ... |
As you can see, the datetime column contains dates like '15-Jan-2019' and '14-Jan-2019'. However, when I read this data into MATLAB, the datetime column is being read as numbers like `[43480]` and `[43479]`. I need to convert these numbers into actual dates in the 'dd-mmm-yyyy' format.
Here's how it's read in MATLAB:
{'Date'} {'Price'} {'Open'} {'High'} {'Low'} {'Vol.'} {'Change %'}
{[43480]} {[152.8000]} {[150.2700]} {[152.8900]} {[150.0600]} {'16.82M'} {[0.0187]}
{[43479]} {[150]} {[150.8500]} {[151.2400]} {[149.2200]} {'32.44M'} {[-0.0150]}
{[43476]} {[152.2900]} {[152.8800]} {[153.7000]} {[151.5100]} {'27.02M'} {[-0.0098]}
{[43475]} {[153.8000]} {[152.5000]} {[153.9700]} {[150.8600]} {'35.78M'} {[0.0032]}
{[43474]} {[153.3100]} {[151.2900]} {[154.5300]} {[149.6300]} {'45.10M'} {[0.0170]}
Here's the relevant part of my code:
% Load the XLSX file
[~, ~, raw] = xlsread('AAPL Historical Data.xlsx');
% Extract the date data
dateColumnIndex = find(strcmp(columnHeaders, 'Date'));
dateData = cell2mat(data(:, dateColumnIndex)); % This part is not working
I have tried using the `cell2mat` function to extract the date numbers from the Excel file, but it doesn't seem to be working as expected.
How can I correctly convert these date numbers into their corresponding actual dates (e.g., '15-Jan-2019') in MATLAB?
  1 个评论
Voss
Voss 2023-9-26
Can you upload the xlsx file (or if it's too big to upload, at least upload a smaller version containing say the first 100 rows of data or something)? Use the paperclip icon to upload a file.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

the cyclist
the cyclist 2023-9-26
An alternative solution to my other one, but possibly more expedient for you in working with existing code, would be to use the datetime function to convert your Excel serial dates into datetime objects:
% Load the XLSX file
[~, ~, raw] = xlsread('AAPL Historical Data.xlsx');
% Extract the date data
dateColumnIndex = find(strcmp(columnHeaders, 'Date'));
dateData = cell2mat(data(:, dateColumnIndex)); % This part is not working
dt = datetime(dataData,'ConvertFrom','excel');
I am not 100% of the syntax (and accuracy) of this method in your case. Again, seeing your data would help.

更多回答（1 个）

the cyclist
the cyclist 2023-9-26
As it says on the documentation page, use of xlsread is not recommended.
You will almost certainly be on a happer road if you read the file with
data = readtable('AAPL Historical Data.xlsx');
and then extract the column you want, probably with
dateData = data.Date;
which should give you a datetime object.
As @Voss has suggested, uploading your input file would allow us to work with your data and verify possible solutions.
  2 个评论
Teoman
Teoman 2023-9-26
编辑：Teoman 2023-9-26
It is going through a loop reading the current excel file after another and I get the error after trying `dateData = data.Date;`:
Dot indexing is not supported for variables of this type.
the cyclist
the cyclist 2023-9-26
OK. It might work with
dateData = data(:,"Date");
But, it's difficult to diagnose without seeing the data.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by