change in xlrange of xlsread while sing loop

Hi

I am using xlsread with different ranges. I am trying to read excel data which the ranges differ in each iteration of loop according to the number of days in a month and in each year. How can I define these changes in xlRange? My code snippet is as follows that can show my intention. My excel file is also attached.

Note: I am puting these data to a cell file with struct of the years.

years= 20
Observation = cell(years);
N=31
for yy=1:years
  year=2000+yy-1
  for month=1:12
    for day=1:N
      if month==4| month==6| month==9| month==11
        N=30
      elseif month==2 
        N=28
      else year==2000& month==2| year==2004& month==2| year==2008& month==2| year==2012& month==2| year==2016& month==2
        N=29
      end
      Data= xlsread( Data , 'sheet1' , [range???? ':' range???]); % my question is here?
      Observation{yy}.Observation{month, day} =Data
    end 
  end
end

[Jan]

The determination of the number of days per month is not working corretly: The Februrary gets 28 days in all cases: "if month==2" catchs all Februraries and the following if branch is not entered. The 31 is set in the first iteration only.

Better:

if any(month == [4, 6, 9, 11))
  N = 30;
elseif month == 2 
  N = 28 + (~mod(year, 4) & (mod(year, 100) | ~mod(year, 400)));
else
  N = 31;
end

The command cell(years) creates a 20x20 cell matrix. I guess you want cell(1, years).

[Honey]

@Jan thanks a lot for your suggestion about the determination of the number of days. It is also better than my code's appearance. And yes it is what I want: cell(years, 1). But I have another cell(20,20) with the content. How can I change the size of it to cell(20,1) that the content moved to the new cell?

I need this cell to compare with that one. So if I produce this cell(20,1), the other is cell(20,20). How to convert it with content? I have just one column in that cell(20,20).

Answer 1

[dpb]

That's the hard way to go at it...just read the spreadsheet into a table and operate over the regions of interest -- simple example

>> tdData=readtable('Data.xlsx');                     % read the spreadsheet to the table
>> head(tData)                                        % sample of what get...
ans =
  8×4 table
    amount    year    month    day
    ______    ____    _____    ___
      0       2000      6       1 
      0       2000      6       2 
      0       2000      6       3 
      0       2000      6       4 
      0       2000      6       5 
      0       2000      6       6 
      0       2000      6       7 
      0       2000      6       8 
>> format bank, format compact                          % show two decimal places
>> groupsummary(tData,{'year'},"sum",{'amount'})        % total amounts by year
ans =
  18×3 table
    year    GroupCount    sum_amount
    ____    __________    __________
    2000.00      214.00        507.00  
    2001.00      365.00       1739.00  
    2002.00      365.00       1113.50  
    2003.00      365.00       1460.50  
    2004.00      366.00       1537.00  
    2005.00      365.00        862.00  
    2006.00      365.00       1411.00  
    2007.00      365.00       1054.50  
    2008.00      366.00       1089.00  
    2009.00      365.00       1128.50  
    2010.00      365.00        839.00  
    2011.00      365.00       1474.50  
    2012.00      366.00       1297.00  
    2013.00      365.00       1043.00  
    2014.00      365.00       1781.00  
    2015.00      365.00       1592.80  
    2016.00      366.00        854.30  
    2017.00       79.00          0.00  
>> 

I just did by year so output would be relatively short; you can do monthly or daily or combinations at will.

The other is doable although I don't recomend it as the way to deal with the data you have -- the logic can be simplified significantly with builtin MATLAB functions...

>> cumDays=0;
   for yy=1:3
      for m=1:4
        cumDays=cumDays+eomday(yy+1999,m)
   end,end
cumDays =
         31.00
cumDays =
         60.00
cumDays =
         91.00
cumDays =
        121.00
cumDays =
        152.00
cumDays =
        180.00
cumDays =
        211.00
cumDays =
        241.00
cumDays =
        272.00
cumDays =
        300.00
cumDays =
        331.00
cumDays =
        361.00
>> 

shows how to accumulate the days in a year accounting for leap years and days in each month.

The problem is your file doesn't begin on Jan 1 of a given year and so unless you read it first, you don't know where to begin counting.

It's far easier and better to just use the data itself.

NB: xlsread has been deprecated in favor or readtable and friends; they peform much faster and in general are far better to use with new code.

[Honey]

I didn't think that it will be so complicated!!!

I wanted just to define variable ranges for xlsread according to each loop.

Look at this cell screenshot which I want to have my data like this.

As I have my data in pixel to pixel, sorting my data in a cell is more visualizable to me.

[dpb]

Well, you can have your data however you want it, but rearrange it after you read it, not while you're trying to read it.

It's far more complicated to build those range variables than to use the data itself to select from/with what you want and how you want it, particularly given the problem that you don't know a priori from whence to start counting because the data begin at an arbitrary point. Unless, of course, you manually enter the start location every time which pretty-much defeats the point of having a programming tool to help.

[dpb]

And I have no idea what am supposed to make of the empty image you attached -- explain/illustrate what you think that represents in the data file if you want somebody to try to build whatever it is from the given data.

Just looking at your original code, I would submit that almost any use I can imagine of data by year/month/day can't be far more easily processed by a table with rowfun, groupsummary and friends or as a timetable and retime and grouping variables if there are other commensurate variables besides just the one observation in the attached data set.

Will be MUCH easier than iterating through a nested structure array...

Answer 2

[Jan]

Why do you want to import ranges at all? Import the complete file as a matrix or table and filter out the needed values by something like:

year  = AllData(:, 2);
month = AllData(:, 3);
thisAmount = AllData(year == 2008 & month == 2, 1);