MATLAB Answers

Import excel data from web doesn't work

5 views (last 30 days)
Adriano
Adriano on 25 Sep 2020
Commented: Adriano on 28 Sep 2020
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,

  0 Comments

Sign in to comment.

Accepted Answer

Michael Croucher
Michael Croucher on 27 Sep 2020
Edited: Michael Croucher on 27 Sep 2020
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'
};

  1 Comment

Adriano
Adriano on 28 Sep 2020
It's perfect Michael! You help me a lot. Many thanks!

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!