Import excel data from web doesn't work
2 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Adriano
am 25 Sep. 2020
Kommentiert: Adriano
am 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 Kommentare
Akzeptierte Antwort
Michael Croucher
am 27 Sep. 2020
Bearbeitet: Michael Croucher
am 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'
};
Weitere Antworten (0)
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!