Reading a Datetime Column as Numbers Instead of Actual Dates in MATLAB
7 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Teoman
am 26 Sep. 2023
Kommentiert: the cyclist
am 26 Sep. 2023
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 Kommentar
Voss
am 26 Sep. 2023
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.
Akzeptierte Antwort
the cyclist
am 26 Sep. 2023
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.
2 Kommentare
the cyclist
am 26 Sep. 2023
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.
Weitere Antworten (1)
the cyclist
am 26 Sep. 2023
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;
As @Voss has suggested, uploading your input file would allow us to work with your data and verify possible solutions.
2 Kommentare
the cyclist
am 26 Sep. 2023
OK. It might work with
dateData = data(:,"Date");
But, it's difficult to diagnose without seeing the data.
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!