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,
采纳的回答
Michael Croucher
2020-9-27
编辑：Michael Croucher
2020-9-27
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'
};
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
