From excel link to time table

5 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)

Categories

Find more on Data Import and Analysis in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by