From excel link to time table

4 views (last 30 days)
Adriano
Adriano on 17 Mar 2020
Commented: Adriano on 17 Mar 2020
Hi all,
I have to download an excel file from this link: https://app2.msci.com/eqb/currency/performance/90463.48.all.xls and than to get a timetable like:
3×1 timetable
Time Var1
__________ ______
31-10-1997 652.4
03-11-1997 661.78
04-11-1997 661.5
I was trying with this code:
msci_em_currency = urlread('https://app2.msci.com/eqb/currency/performance/90463.48.all.xls')
temp1 = strfind(msci_em_currency,'(USD)');
temp2 = strfind(msci_em_currency,'This information');
msci_em_currency = msci_em_currency(temp1+6:temp2(1,1)-3)
However I don't know how to separate dates from data in a char variable. Someone can help me? Thanks!!

Accepted Answer

Guillaume
Guillaume on 17 Mar 2020
First note that urlread has been deprecated for a while. Its replacement is webread which is more powerful.
webread would have been able to import the data from the url directly as a table if not for the file that it has a misleading xls extension. The file is not an excel file, it's a plain text file and unfortunately webread attempts to decode it as an excel file and so fails. I could not find a way to override that.
So a workaround is to use websave to save the file locally, then import it with readtimetable:
url = 'https://app2.msci.com/eqb/currency/performance/90463.48.all.xls'; %Despite the xls extension, the file at that URL is a plain text file!
tempfile = [tempname(), '.txt']; %ask matlab for a temporary filename. Give it a txt extension so readtimetable decodes it as text
websave(tempfile, url);
msci_em_currency = readtimetable(tempfile);
delete(tempfile); %delete temporary file. No longer needed.
  1 Comment
Adriano
Adriano on 17 Mar 2020
Great! I didn't know it wasn't an excel file. Thanks a lot!

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!