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

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?

[Voss]

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.

Answer 1

[the cyclist]

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.

[Teoman]

This works thank you!

[the cyclist]

I would highly recommend spot-checking your MATLAB dates with what you see in the file, just to make sure this conversion gives what you expect.

Answer 2

[the cyclist]

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.

[Teoman]

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]

OK. It might work with

dateData = data(:,"Date");

But, it's difficult to diagnose without seeing the data.