MATLAB: Create a table with Revenue by financial year such as 2017-18

summation

From the excel data attached I’m trying to create a table with Revenue by financial year such as 2017-18 (where financial year is the sum of the quarters September 2017 + December 2017 + March 2018 + June 2018).
The table below shows what I need to get to from the raw data I attach. I tried writing the code attached but this sums the Revenue for all periods to give me a total of 8.8 for the Code category ‘abc’.
Do you know how I can do this table as below?
Code    2017-18    2018-19
abc        4                4.8
def         6.2            6.4
ghi         8               12

Best Answer

t=readtable('chindamo.xlsx');                                % read the input table data
t.Code=categorical(t.Code);                                  % I just like categorical for such...
t.AcctYear=fix((t.Date-t.Date(1))/duration(24*365,0,0));     % compute index variable of accounting year
g=findgroups(t.AcctYear,t.Code);                             % get the grouping variable by year, code
fnRev=@(q,p) sum(q.*p);                                      % define revenue summation functional
revenue=splitapply(fnRev,t.Quantity,t.Price,g);              % and calculate for each group
revenue=reshape(revenue,numel(categories(t.Code)),[]);       % orient by code for each year
returns the array of values
>> revenue=reshape(revenue,numel(categories(t.Code)),[])
revenue =
          4.00          4.80
          6.20          6.40
          8.00         12.00
>>
from which you can build whatever structure you wish...
NB: the "trick" here is to compute the accounting year indicator variable as the integer portion of a duration of days. This relies on the data being ordered such that the first date of the desired year start month is the first element in the date vector as it simply is checking for the multiple of 365 days. It would take some fixup logic to deal with leap years.
The retime function with a timetable could be made to work by generating a specific new time vector; it's unfortunate that its builtin periods are based solely on a Jan1 - Dec31 definition of year start/end; it seems a logical extension to be able to define also the DOY that a fiscal year starts/ends; there certainly are sufficient uses for such. It's possible the Financial Toolbox might have such things; I don't have it and haven't searched...
ADDENDUM:
The general solution for the duration vector is:
t.AcctYear=fix((t.Date-t.Date(1))./duration(24*365+isleapyr(tt),0,0);
where
function is=isleapyr(yr)
%  returns T for input year being a leapyear
if isdatetime(yr), yr=year(yr); end
is=eomday(yr,2)==29;
Related Question