Pulling Data from ledger to organize in average monthly balances

[smurad008]

I have an excel attached that has data on the first tab and the 2nd tab shows what I want to do. I can't seem to figure it out because of the dates not being exactly at the end of the month. I basically want to pull the most beginning month date number and the most end month date number then organize it in a table then calculating the month average.Any help would be awesome I am new to this and could use a hand I have been working on this for hours and not getting anywhere. 

Any help is appreciated! Thank you ahead of time. 

[grossal · Accepted solution]

Hi @smurad008,

I got pretty close to what you were looking for:

Result:

What happens in the worklow:

- Renaming columns (Select)

- Removing empty rows (Sample)

- Calculating Year n Month column (Formula)

- Filling up columns (2x Multi-Row-Formula)

- Filtering into Investment and Checking (Filter)

- Extracting first and last row (Sample)

- Adapting the date to last/first (Formula)

- Union all together (Union)

- Formula to fill up columns (Formula)

- Sort for checking if I did it correctly (Sort) [you can remove this]

- Cross Tab to bring everything into shape (Cross Tab)

- Select to bring Checking to the front (Select)

I am a bit confused about the Sample Result. Your column "2370_ABC PA Equity" shows a little different value for beginning. How do you "know" the beginning value? I assumed that the first value of a month is the beginning and the last value of a month is the end. I'll attach what I got. Let's work this out together!

Best

Alex