Extract the latest Account Balance based on dates

[Limor_OP]

Hello,

I need your help please.
I have a file I am downloading from the bank, and need to extract the balances of all accounts for the end of July.

The file can contain a range of dates from the 1st of Jul.- Jul.31st . A balance will show depending if there were transactions on that date.
If there were no transactions it will skip to the next day.


I have 2 problems:

1. The file is showing some of the dates as a text, which makes it difficult to use for calculations.
2. Some of the balances will end at 30.7 and some on 31.7
I need a formula to take the most recent balance.


I attached an example hereby.

Thank for your help.

[AliGW]

Still Office 2010, or something newer?

[Limor_OP]

yes, 2010.

[BMV]

I'm not sure your regional settings allows you to convert text to data use -- but i hope it will be

P.S. Sorry i couldn't insert formulas to the message. Site firewall rejected it

[Limor_OP]

Thanks BWV
I will copy the formulas:
E4:
IFERROR(INDEX($A$4:$A$97,MATCH(0,COUNTIFS($E$3:E3,$A$4:$A$97),)),"")

G4:
=IFERROR(INDEX(B:B,0.1+MOD(AGGREGATE(14,6,($C$4:$C$97+ROW($C$4:$C$97) %% )/($A$4:$A$97=E4)/(-- ($C$4:$C$97)>=$E$2)/(--($C$4:$C$97)< EDATE($E$2,1)),1),1)/1 %%),"")

The problem is that the balances returned are not for the last day of the month.

For example - account 3-101 returns 66.29 while it should return 70.64 (for Jul. 30) because it recognizes Jul. 09 as Sep. 07th
If I download the report as CSV - can it fix the dates issue?

Thank you!

[BMV]

What is the correct result 66.29 or 70.64 ? In My file it's 70.64.

[Limor_OP]

Probably indeed regional settings.
the dates are supposed to relate to July only.

my File is showing :

1-001 53032
5-330 18.13
2-001 11599
3-101 66.29

The dates for account 3-101 are showing:

66.29 7-Jan-2020
66.29 7-Feb-2020
66.29 7-Mar-2020
66.29 7-May-2020
66.29 7-Jun-2020
66.29 7-Jul-2020
66.29 7-Aug-2020
51.39 20/07/2020
51.39 21/07/2020
51.39 22/07/2020
51.39 23/07/2020
51.39 24/07/2020
51.39 26/07/2020
12.07 17/07/2020
12.07 19/07/2020
12.07 15/07/2020
12.07 16/07/2020
84.97 13/07/2020
84.97 14/07/2020
6.29 7-Oct-2020
6.29 7-Dec-2020
70.64 28/07/2020
70.64 29/07/2020
70.64 30/07/2020
51.36 27/07/2020
66.29 7-Sep-2020

[Limor_OP]

What can I do with the dates? some are displayed as text , and some are not. Those that are showing as a date switched the month with the day :/// so Jul. 09 became Sep. 07th , etc.....

[alansidman]

I used Power Query and converted all the dates to US format. Here is the Mcode

Please Login or Register  to view this content.

[Fluff13]

You can use Text to columns to convert the dates to real dates.
on page 3 select DMY (It must be the format the dates are in, not your regional settings)

[Limor_OP]

Thank you All.
Fluff - I did what you said and now BMV 's formula works perfectly.

Alan - thank you too! this is a few levels above my capabilities

[Fluff13]

You're welcome & thanks for the feedback.

[AliGW]

Glad to help.

[alansidman]

Belinda
If you want to explore Power Query then I suggest you get a copy of "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Worth learning and not as difficult as VBA/

[Limor_OP]

I wish I had the time to learn. Where is this book available, and how much does it cost?

[AliGW]

Amazon: https://www.amazon.co.uk/s?k=m+is+fo...l_90mwa9xjd9_e

The third book on that page by Gil Raviv is pretty handy, too. I have both.