From excel link to time table

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!!

Answer 1

[Guillaume]

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.

[Adriano]

Great! I didn't know it wasn't an excel file. Thanks a lot!