Import excel data from web doesn't work

Hi all,

I need to import some data downloaded from an excel url. To do it, I download the excel on my pc (even if i don't need the file) and than I use readtable function to read the the excel file. This is my code:

reit_excel = websave('FTSE NAREIT All REIT index','https://www.reit.com/sites/default/files/returns/MonthlyHistoricalReturns.xls');
nareit_reit = readtable('FTSE NAREIT All REIT index.xls','FileType','spreadsheet');

The output of this code is a NaN columns. Someone knows why and how to fix the problem? Many thanks!

Regards,

Answer 1

[Michael Croucher]

The main issue is that MATLAB doesn't recognise what Sheet name within that spreadsheet that you want to import. This gets you a lot further:

data = readtable('FTSE NAREIT All REIT index.xls','Sheet','Index Data')

However, the result is still pretty messy. A rather cleaner table can be obtained as follows.

% Try to auto detect import options
opts = detectImportOptions('FTSE NAREIT All REIT index.xls','Sheet','Index Data');
% Some variables get incorrectly imported as char.  Change that to double
opts = setvartype(opts,'RealEstate50TM','double');
opts = setvartype(opts,'Var17','double');
opts = setvartype(opts,'Var18','double');
opts = setvartype(opts,'Var19','double');
opts = setvartype(opts,'Var20','double');
opts = setvartype(opts,'Var21','double');
% Do the import
data = readtable('FTSE NAREIT All REIT index.xls',opts);
% Delete the empty variables
data = removevars(data,{'Var8','Var15','Var22','Var29','Var36'});
% Rename the variables to something more readable
data.Properties.VariableNames = {'Date','All_REITs_Total_Return','All_REITs_Total_Index',...
   'All_REITs_Price_Return','All_REITs_Price_Index','All_REITs_Income_return','All_REITs_Dividend_Yield',...
   'Composite_Total_Return','Composite_Total_Index',...
   'Composite_Price_Return','Composite_Price_Index','Composite_Income_return','Composite_Dividend_Yield',...
   'RE50_Total_Return','RE50_Total_Index',...
   'RE50_Price_Return','RE50_Price_Index','RE50_Income_return','RE50_Dividend_Yield',...
   'All_Equity_Total_Return','All_Equity_Total_Index',...
   'All_Equity_Price_Return','All_Equity_Price_Index','All_Equity_Income_return','All_Equity_Dividend_Yield',...
   'Equity_Total_Return','Equity_Total_Index',...
   'Equity_Price_Return','Equity_Price_Index','Equity_Income_return','Equity_Dividend_Yield',...
   'Mortgage_REITs_Total_Return','Mortgage_REITs_Total_Index',...
   'Mortgage_REITs_Price_Return','Mortgage_REITs_Price_Index','Mortgage_REITs_Income_return','Mortgage_REITs_Dividend_Yield'
   };

[Adriano]

It's perfect Michael! You help me a lot. Many thanks!