How to import Excel data in MATLAB in Mac

38 次查看（过去 30 天）
AngeloCSM
AngeloCSM 2020-4-29
评论： AngeloCSM 2020-4-29
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
  2 个评论
the cyclist
the cyclist 2020-4-29
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.
AngeloCSM
AngeloCSM 2020-4-29
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

请先登录，再进行评论。

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

采纳的回答

Guillaume
Guillaume 2020-4-29
"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
  5 个评论
显示 3更早的评论
Guillaume
Guillaume 2020-4-29
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.

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

产品


版本

R2020a

Community Treasure Hunt

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

Start Hunting!

Translated by