SOLVED

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

Copper Contributor

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

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/Account AC01 AC02 AC03 AC04 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/YTD AC01 AC02 AC03 AC04 2016 2017 2018 2019 2020 2021 2022 2023

13 Replies

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

``=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.

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

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:

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

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

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

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

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

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.

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

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

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

Better ?

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

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

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

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

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)

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

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

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

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

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

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

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

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

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

Better ?

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