MATLAB Answers

Import excel data from web doesn't work

6 views (last 30 days)
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','');
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!


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',...

  1 Comment

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!