Add date variable into Xlsread Syntax?? Needs Help!!

Guys I really need some help here!

There are mutiple similar xlsx files mamed after date, '199001.xlsx'(Jan 1990),'199002.xlsx','199003.xlsx'... Those files include data in different dates with accuracy in month.

The first step is to use MATLAB to do manipulate the data in one month/file. Then do a loop(since the manipulation algorithm for each month is the same), and pull out all the results in the same spreadsheet.

I've done the manipulation for one month. My problem right now is:

How can to make the date a variable when using xlsread?

Case 1, when I use

[~, ~, XXX] = xlsread('C:\Users...\199001.xlsx','Sheet1','E3:E502')

How can I make the part '199001' a variable?

Except for adding a variable to xlsread syntax, it is more challenging when '199001' is a date because the series should be:

199001,199002,199003... 199011,199012,199101(not '199013'!),199102...

So it's not a simple i = i+1 loop.

%%%%%%%%%%%%%

Case 2

There is another file called Economics.xls containing Economics information for all months. Part of the whole spreadsheet is like

When the loop comes to '199708' I should read data in Row 2, i.e., 5.54,0.00,2.23.

The code is

XXX = xlsread('C:\...\Economics.xls','Sheet1','B2:D2')

How can I make Row 2 as a variable according to the first column(the date)? It looks like this should be the same variable in Case 1.

Your opinion is very important!

Answer 1

[Andrei Bobrov]

case 1

in case: your files from 199001.xlsx to 199108.xlsx

b = diff(datevec({'199001';'199108'},'yyyymm'));
mns = b(1:2)*[12;1];
a = cellstr(datestr(datenum(1990,1+(0:mns)',1),'yyyymm'));
XXX = cell(size(a));
[~,~,XXX] = cellfun(@(x)xlsread(fullfile(yourpath,[x,'.xlsx']),'Sheet1','E3:E502'),a,'un',0);

[Xiao Tang]

Hey Andrei,

Thanks for replying my question in 3 am. However, when I ran the first three lines, it shows,

   Error using dtstr2dtvecmx
  Failed on converting date string to date number.

Error in datevec (line 119)
            y = dtstr2dtvecmx(t,icu_dtformat);

The wired thing is when I debug line by line, those three lines works well without error!

[Andrei Bobrov]

please try this is code:

[Y M] = datevec(datenum({'199001';'199108'},'yyyymm'))
mns = diff([Y M])*[12;1]
a = cellstr(datestr(datenum(1990,1+(0:mns)',1),'yyyymm'))

[Xiao Tang]

It's perfect! Thanks