How to import Excel data in MATLAB in Mac

I need to import Excel sheets that mainly contain dates and financial instrument prices. I have read that xlsread does not work on Mac and I would like to know if someone could explain why and if there are work arounds.

I have tried readtable but it stores the dates in numbers and also it gives me errors when trying to convert these into arrays. I have also tried converting the excel into CSV but that seems to work. I also get errors whe trying to apply MATLAB functions like yearfrac and datenum on manually imported data.

Is there any way to circumvent a manual import or installing a windows virtual machine?

Thank you

[the cyclist]

I suggest that you post the Excel file, or a small representative sample, so that we can test some code on it rather than give you abstract answers.

[Angelo Corrado Sabino Manna]

Here is the Excel file I am working on and the type of importing I should accomplish.

function [dates, rates] = readExcelData( filename, formatData)
% Reads data from excel
%  It reads bid/ask prices and relevant dates
%  All input rates are in % units
%
% INPUTS:
%  filename: excel file name where data are stored
%  formatData: data format in Excel
% 
% OUTPUTS:
%  dates: struct with settlementDate, deposDates, futuresDates, swapDates
%  rates: struct with deposRates, futuresRates, swapRates

%% Dates from Excel

%Settlement date
[~, settlement] = xlsread(filename, 1, 'E8');
%Date conversion
dates.settlement = datenum(settlement, formatData);

%Dates relative to depos
[~, date_depositi] = xlsread(filename, 1, 'D11:D18');
dates.depos = datenum(date_depositi, formatData);

%Dates relative to futures: calc start & end
[~, date_futures_read] = xlsread(filename, 1, 'Q12:R20');
numberFutures = size(date_futures_read,1);

dates.futures=ones(numberFutures,2);
dates.futures(:,1) = datenum(date_futures_read(:,1), formatData);
dates.futures(:,2) = datenum(date_futures_read(:,2), formatData);

%Date relative to swaps: expiry dates
[~, date_swaps] = xlsread(filename, 1, 'D39:D88');
dates.swaps = datenum(date_swaps, formatData);

%% Rates from Excel (Bids & Asks)

%Depos
tassi_depositi = xlsread(filename, 1, 'E11:F18');
rates.depos = tassi_depositi / 100;

%Futures
tassi_futures = xlsread(filename, 1, 'E28:F36');
%Rates from futures
tassi_futures = 100 - tassi_futures;
rates.futures = tassi_futures / 100;

%Swaps
tassi_swaps = xlsread(filename, 1, 'E39:F88');
rates.swaps = tassi_swaps / 100;
end

Answer 1

[Guillaume]

"I have read that xlsread does not work on Mac and I would like to know if someone could explain why and if there are work arounds."

This is not exactly true. xlsread works on a Mac if use the 'basic' option (which is on by default on Macs I believe). With the 'basic' option xlsread parses the excel file directly instead of communicating with excel to extract the data. However, with sufficiently complex excel files, xlsread may not parse the file correctly.

Even if you can use xlsread on a mac, you're still better off using readtable, readmatrix, and co. They're improvement over xlsread. By default in R2020a, readtable parses the excel file directly and works on a Mac. Just like with xlsread, there may be instances where this process fails but in general it shouldn't.

If you want to know the nitty-gritty, xlsread when not in basic mode, and readtable when 'UseExcel' is true, start Excel, ask Excel to open the file, then ask excel for the content of the spreadsheet it has read. This guarantees that the file is read correctly since it's excel doing it but the communication with excel is is only possible on Windows since the mechanism it uses (called COM or ActiveX) is only available on Windows. It would require cooperation between Apple and Microsoft to be implemented on Macs, I wouldn't hold my breath...

"I have tried readtable but it stores the dates in numbers"

That shouldn't be the case but if it happens you should be able to convert the numbers back to date using the 'ConvertFrom', 'excel' option of datetime.

"it gives me errors when trying to convert these into arrays"

You've done something wrong then. Without details of what you're doing nor the text of the error message, it's hard to help you.

" I have also tried converting the excel into CSV but that seems to work. I also get errors whe trying to apply MATLAB functions like yearfrac and datenum on manually imported data."

Again, not enough details about what you're doing or the errors.

"Is there any way to circumvent a manual import or installing a windows virtual machine?"

Use readtable, readmatrix, or readcell. In your case, I'd use readcell to read the whole spreadsheet in then read the required data out of the cell array. Note that the design of your spreadsheet is great for a human reader, but really not ideal for processing by a program.

allcontent = readcell(filename); %should work the same on a Mac as on windows
dates.settlement = allcontent{8, 5};  %should already be a datetime which is much better than a datenum. Don't convert to datenum!
dates.depos = cell2mat(allcontent(11:18, 4));  %already datetime
dates.futures = cell2mat(allcontent(12:20, 17:18)); %again nothing more to do
%etc. for the rest of the file. Use cell2mat to extract the relevant portion of the cell array

[Angelo Corrado Sabino Manna]

Hi Guillame,

I tried the code you posted but this is the error I got (also the error I previosuly had when playing around with manual import into cell)

[Guillaume]

Oh yes, I completely forgot that, frustratingly, cell2mat doesn't work with cell arrays of datetime. Define a simple anonymous function that does the same for cell arrays of scalar anything:

cell2arr = @(c) reshape([c{:}], size(c));

and use that instead of cell2mat, so:

allcontent = readcell(filename); %should work the same on a Mac as on windows
dates.settlement = allcontent{8, 5};  %should already be a datetime which is much better than a datenum. Don't convert to datenum!
dates.depos = cell2arr(allcontent(11:18, 4));  %already datetime
dates.futures = cell2arr(allcontent(12:20, 17:18)); %again nothing more to do
%etc. for the rest of the file. Use cell2arr to extract the relevant portion of the cell array

[Angelo Corrado Sabino Manna]

Thank you again, I even managed to write a function that imports the data by specifying range etc. Could you clear up one last thing?

How come is datetime better than datenum? Later in the project I will need datenum so how can I avoid getting this error:

dates = 

  struct with fields:

    settlement: 19-Feb-2008
         depos: [8×1 datetime]
       futures: [9×1 datetime];
       
formatData = 'dd/mm/yy'

dates.futures(:,1) = datenum(date_futures_read(:,1), formatData);
dates.futures(:,2) = datenum(date_futures_read(:,2), formatData);

Error using datetime/datenum
Too many input arguments.

[Guillaume]

There are many advantages to datetime over datenum, it covers a much larger range of dates, it takes into account timezones and leap seconds, you can change the display format without affecting the underlying stored date, maths with datetime are easier.

datetime was created because there were many problems with datenum. datetime is also easier to use. I strongly recommend you don't use datenum.

If you do insist to convert datetime into datenum, then you do not need to and mustn't specify a format:

asdatenum = datenum(datetimearray);  %format can't be specified since datetime stores the true time regardless of the DISPLAY format.

"Later in the project I will need datenum"

Whatever you're doing with datenum, you can do with datetime, most likely more easily.

[Angelo Corrado Sabino Manna]

Thank you so much for everything! :)