SOLVED

Calculating annual (& YTD) account credits from a dated record of monthly credits

Copper Contributor

Hi...Been struggling to solve the following problem with Excel 365 and hoping someone can help. Here is a summary. Thanks in advance for any help and suggestions. Fraser.

 

There 20 different accounts. Since the particular date on which each account was opened, a payment is made to the account on the first of every month thereafter without fail. The date on which each account was opened, together with the record of corresponding monthly payments are available in a table, together with the date and change to the monthly payment. Future monthly payment changes are also recorded in the table. The problem is how to populate a table summarising the amount paid into each account in each of the preceding calendar years and in the current calendar year to date. A sample table with a reduced number of accounts and data is shown below. Dates are dd/mm/yyyy

 

Date/AccountAC01AC02AC03AC04
01/02/2016£650   
01/05/2016 £65 £50
01/02/2017 £100£15 
01/05/2017 £200 £75
01/11/2018  £20 
01/03/2020  £35 
01/06/2021£660   
01/06/2023  £50 
01/02/2024£700   
Credits pa/YTDAC01AC02AC03AC04
2016    
2017    
2018    
2019    
2020    
2021    
2022    
2023    

 

 

  

 

 

13 Replies

@PhraserB 

=VSTACK({""."AC01"."AC02"."AC03"."AC04"},HSTACK(UNIQUE(YEAR(H2:H10)),MAKEARRAY(COUNTA(UNIQUE(YEAR(H2:H10))),COUNTA(I1:L1),LAMBDA(r,c,SUM(FILTER(CHOOSECOLS(I2:L10,c),YEAR(H2:H10)=INDEX(UNIQUE(YEAR(H2:H10)),r)))))))

You can try this formula.

date account.JPG

Hi @PhraserB 

EDIT: After clarifications below proposal does not apply. Approach could apply to other scenario though

Alternatively Unpivot your accounts with Get & Transform aka Power Query (sample attached). After that almost all analysis are easy, i.e. with a PivotTable:

 

Sample.png

Hi QP....Thank you very much for taking time to reply. The method looks really interesting. However, I realise and must apologise that I have not explained the problem clearly enough. To clarify, for example for AC01; The account was opened on 010216 with a payment of £650 per month. Payments of £650 per month were made on the first of each successive month until 010621 when the monthly payment was increased to £660 per month. This new monthly payment of £660 continued every month thereafter. The intention is increase the monthly payment to £700 on 010224. This would mean that the annual totals for each of the years from 2016-2022 would be the sum of all the monthly credits in each of those years; eg for 2016 the sum would be 11 x £650 = £7150. For the current year, the annual total would depend on the current date and for dates in the future the total would be displayed as zero until that date had occurred. The same applies to all the other accounts. I hope that is clearer. Many thanks Fraser
Hi Lz.... Many thanks for taking time to reply and for recommending using a Pivot Table. I realise and apologise that I have not explained the problem clearly enough. Please see my reply to Quadruple Pawn which gives further information. Thanks again for your help. Fraser

@PhraserB 

You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.account credit.JPG

Hi QP... That's really helpful. Thank you very much for solving the problem for me. Regards Fraser
best response confirmed by HansVogelaar (MVP)
Solution

@PhraserB 

Better ?

Sample.png

EDIT: Added variant - A bit more straightforward (kudos @p45cal for the approach - here)

Hi Lz... Thanks. This is very neat and exactly what I was looking for. Will have a close look tomorrow when I have more time. Thanks again. Brilliant! Regards Fraser

Hi @PhraserB 

Please re-download (inc. Variant). I made a couple of adjustments (same output) - I'll explain later if necessary

 

Did a couple of scalability checks with 50 account running until end of 2050 - works like a charm
FYI: 1st time you Refresh a query within an Excel session takes a bit of more time - to load the .NET Framework. Ex.: with the above sample 1st time to Refresh +/- 5 secs, next Refresh +/- 1 sec. (here)

Good Morning Lz... thank you for providing more information about your solutions, including the update to your original and the variant. The scalability information was helpful because there are currently 20 accounts. The refresh time is good to know and of course isn't a problem. Whilst I've used Excel for many years (I was the Director of Medical Physics in a Scottish hospital for many years), my knowledge of pivot tables has been limited to discussions with the Finance department. In terms of the present problem I'm still exploring the differences between the files you've provided. Although I'm sure it will turn out to be obvious, I'm intrigued by how the current year to date total is constrained by the current date. Thanks again for your help and interest. Best wishes Fraser

Good morning @PhraserB 

I'm intrigued by how the current year to date total is constrained by the current date

 

This happens in the query that's the Source of the PivotTable

If you take a look at the Variant, query TableForPivot:

- At ttep ExpandedAccount ALL amounts are > 0 (inc. those in the future)

- Step CurrentPeriod calculates the EndOfMonth from today's date

- Step ReplacedAmount compares (row by row) if [Period] <= CurrentPeriod. If TRUE it keeps the [Amount] otherwise it replaces it with 0

Hi Lz.... Many thanks for that. Will explore further. Best wishes Fraser

@PhraserB 

 

Hi Lz…. Just a small update. Thank you for suggesting Excel Power Query as the way forward for me. I’ve been working through the information you posted together with some YouTube videos and have realised Power Query provides such an impressive problem solving method. Thank you again. Best wishes Fraser 

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@PhraserB 

Better ?

Sample.png

EDIT: Added variant - A bit more straightforward (kudos @p45cal for the approach - here)

View solution in original post