budget and --average expenses

[Gour]

Hello,

Gnucash has option to provide average value for the monthly
expenses when one is preparing budget and need estimation for monthly
costs.

In the ledger manual
(http://ledger-cli.org/3.0/doc/ledger3.html#Budgeting-and-Forecasting)
there is following example given:

"For help on finding out what your average monthly expenses are for any
category, use a command like:"

$ ledger -p "this year" --monthly --average balance ^expenses


Now, when I run something like this with my ledger file:

$ ledger -f personal.dat -p "this year" --monthly --average balance Hrana

wher 'Hrana' is for food, I get:

33.60 HRK Expenses:Hrana

Now, if we take a look of the total

ledger -f personal.dat -p "this year" --monthly balance Hrana

for the same period we get:

24828.44 HRK Expenses:Hrana


One does not have to be great mathematician to see something is wrong,
but I wonder if there is some helper in Ledger to be used to get monthly
expenses for each category in order to assist when one is preparing a
budget?


Sincerely,
Gour

--
One is understood to be in full knowledge whose every endeavor
is devoid of desire for sense gratification. He is said by sages
to be a worker for whom the reactions of work have been burned
up by the fire of perfect knowledge.

[John Wiegley]

>>>>> Gour <go...@atmarama.net> writes:

> "For help on finding out what your average monthly expenses are for any
> category, use a command like:"

> $ ledger -p "this year" --monthly --average balance ^expenses

I strongly recommend avoiding the balance command with --average, but to use
--monthly --average register instead.

John

[Craig Earls]

I will update the manual.  

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Craig, Corona De Tucson, AZ
enderw88.wordpress.com

[Erik Hetzner]

Hi all,

At Thu, 25 Sep 2014 10:10:49 -0500,

This is what I came up with to get average monthly expenses over a
time period.

ledger bal ^Expenses -E -p "monthly from 2014 until this month" --display-total "(display_total>0 or display_total<0) ? display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"

If somebody has a better solution I'd love to know!

best, Erik

--
Sent from my free software system <http://fsf.org/>.

[Gour]

On Thu, 25 Sep 2014 10:01:59 -0700
Erik Hetzner <ehet...@gmail.com> wrote:

> This is what I came up with to get average monthly expenses over a
> time period.
>
> ledger bal ^Expenses -E -p "monthly from 2014 until this month"
> --display-total "(display_total>0 or display_total<0) ?
> display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"

Thanks a lot. Very useful and another obstacles removed in my gnucash
--> ledger migration. ;)

I'd say such things should go into FAQ and/or manual.

Sincerely,
Gour

--
Not by merely abstaining from work can one achieve freedom
from reaction, nor by renunciation alone can one attain perfection.

[Erik Hetzner]

Hi Gour,

At Thu, 25 Sep 2014 19:40:25 +0200,

Gour wrote:
>
> On Thu, 25 Sep 2014 10:01:59 -0700
> Erik Hetzner <ehet...@gmail.com> wrote:
>
> > This is what I came up with to get average monthly expenses over a
> > time period.
> >
> > ledger bal ^Expenses -E -p "monthly from 2014 until this month"
> > --display-total "(display_total>0 or display_total<0) ?
> > display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"
>
> Thanks a lot. Very useful and another obstacles removed in my gnucash
> --> ledger migration. ;)
>
> I'd say such things should go into FAQ and/or manual.

I should clarify that the above is a bit of a hack and will only work
if you are averaging starting at the beginning of the year until now,
because it divides by the number of the current month.

If somebody has a better way to do it I would love to know. Am I the
only person who wants to know what my average spending in a category
is per month?

[Gour]

On Fri, 26 Sep 2014 23:59:59 -0700
Erik Hetzner <ehet...@gmail.com> wrote:

Hello Erik,

> If somebody has a better way to do it I would love to know. Am I the
> only person who wants to know what my average spending in a category
> is per month?

I must say that I'm surprised about. The example from the manual does
not work and considering that ledger has budgeting feature, it's a bit
strange there is no straight option to do it...even Gnucash has
'estimate' helper to do it.


Sincerely,
Gour

--

[Simon Michael]

On 9/26/14 8:59 PM, Erik Hetzner wrote:
>> On Thu, 25 Sep 2014 10:01:59 -0700
>> Erik Hetzner <ehet...@gmail.com> wrote:
>>> This is what I came up with to get average monthly expenses over a
>>> time period.
>>>
>>> ledger bal ^Expenses -E -p "monthly from 2014 until this month"
>>> --display-total "(display_total>0 or display_total<0) ?
>>> display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"
>

> I should clarify that the above is a bit of a hack and will only work
> if you are averaging starting at the beginning of the year until now,
> because it divides by the number of the current month.
>
> If somebody has a better way to do it I would love to know. Am I the
> only person who wants to know what my average spending in a category
> is per month?

Far from it - everyone wants this sooner or later. When we discussed
this on #ledger in July, my answer was:

ledger reg -MAE '^some:account$' --depth 2

1. for each account you're interested in, one at a time
2. limiting the report depth to that of the account
(to get a single report line per period)
3. ^ and $ to ensure precise account matching
4. -E to show per-period average not per-line average
5. -M for monthly, -W for weekly etc.
6. ledger 3 (or hledger), not ledger 2
7. with current ledger 3, ensure there's some data (maybe in each
period ?) to avoid a segfault bug

Does the more complicated command you gave have advantages ? Maybe it
shows all accounts at once ? I don't have a ledger-compatible journal
here that avoids 7, so sample output would be nice to see.

[Martin Blais]

On a related note, just toying with how this would look in my proposed SQL-like query language, it would require nested statements support:

SELECT
 root_account, AVG(balance)
FROM (
 SELECT
   MAXDEPTH(account, 2) as root_account
   MONTH(date) as month,
   SUM(change) as balance
 WHERE date > 2014-01-01
 GROUP BY root_account, month
)
GROUP BY root_account

[Erik Hetzner]

Hi Simon,

At Thu, 02 Oct 2014 11:50:41 -1000,

Simon Michael wrote:
>
> Far from it - everyone wants this sooner or later. When we discussed
> this on #ledger in July, my answer was:
>
> ledger reg -MAE '^some:account$' --depth 2
>
> 1. for each account you're interested in, one at a time
> 2. limiting the report depth to that of the account
> (to get a single report line per period)
> 3. ^ and $ to ensure precise account matching
> 4. -E to show per-period average not per-line average
> 5. -M for monthly, -W for weekly etc.
> 6. ledger 3 (or hledger), not ledger 2
> 7. with current ledger 3, ensure there's some data (maybe in each
> period ?) to avoid a segfault bug
>
> Does the more complicated command you gave have advantages ? Maybe it
> shows all accounts at once ? I don't have a ledger-compatible journal
> here that avoids 7, so sample output would be nice to see.

Sure, here Is an example. This is one expense account:

$ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month"
$267.64 Expenses:Erik:Bicycle

That is my total bike spending in 2014. The number I am looking for is
$267.64 / 9 => $29.74, my average monthly spending in 2014 (until
Sept) on bike expenses. So this command gives me what I am looking
for:

$ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month" --display-total "(display_total>0 or display_total<0) ? display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"
$29.74 Expenses:Erik:Bicycle

Moreover, if I don't restrict it to that one account, it works for all
accounts, giving me my average monthly spending in each expense
category for the year.

Your command gives me:

$ hledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
2014/01/01 - 2014/01/31 Expenses:Erik $22.00 $22.00
2014/02/01 - 2014/02/28 Expenses:Erik $23.07 $22.54
2014/03/01 - 2014/03/31 Expenses:Erik $37.24 $27.44
2014/04/01 - 2014/04/30 Expenses:Erik $157.00 $59.83
2014/05/01 - 2014/05/31 0 $47.86
2014/06/01 - 2014/06/30 0 $39.89
2014/07/01 - 2014/07/31 Expenses:Erik $28.33 $38.23
2014/08/01 - 2014/08/31 0 $33.46
2014/09/01 - 2014/09/30 0 $29.74

which does give me the right answer at the end ($29.74) but outputs a
lot more and only works on one account. ledger doesn't seem to work
properly:

$ ledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
14-May-01 - 14-May-31 <None> 0 $47.86
14-Jun-01 - 14-Jun-30 <None> 0 $39.88

Maybe what I am looking for is strange, but for me it makes sense.

[Simon Michael]

Thanks! I have questions:

On Oct 2, 2014, at 7:18 PM, Erik Hetzner <ehet...@gmail.com> wrote:
> Sure, here Is an example. This is one expense account:
>
> $ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month"
> $267.64 Expenses:Erik:Bicycle
>
> That is my total bike spending in 2014.

I’m confused by the “monthly” - my guess it is has no effect in these ledger balance commands ?

> The number I am looking for is
> $267.64 / 9 => $29.74, my average monthly spending in 2014 (until
> Sept) on bike expenses. So this command gives me what I am looking
> for:
>
> $ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month" --display-total "(display_total>0 or display_total<0) ? display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"
> $29.74 Expenses:Erik:Bicycle

Nifty, could you explain the divisor expression a little more ? I don’t quite understand how it’s working.

> Moreover, if I don't restrict it to that one account, it works for all
> accounts, giving me my average monthly spending in each expense
> category for the year.
>
> Your command gives me:
>
> $ hledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
> 2014/01/01 - 2014/01/31 Expenses:Erik $22.00 $22.00
> 2014/02/01 - 2014/02/28 Expenses:Erik $23.07 $22.54
> 2014/03/01 - 2014/03/31 Expenses:Erik $37.24 $27.44
> 2014/04/01 - 2014/04/30 Expenses:Erik $157.00 $59.83
> 2014/05/01 - 2014/05/31 0 $47.86
> 2014/06/01 - 2014/06/30 0 $39.89
> 2014/07/01 - 2014/07/31 Expenses:Erik $28.33 $38.23
> 2014/08/01 - 2014/08/31 0 $33.46
> 2014/09/01 - 2014/09/30 0 $29.74
>
> which does give me the right answer at the end ($29.74) but outputs a
> lot more and only works on one account.

Right, I forgot to mention “8. the average you want is the last number in the running total column”.

> ledger doesn’t seem to work properly:
>
> $ ledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
> 14-May-01 - 14-May-31 <None> 0 $47.86
> 14-Jun-01 - 14-Jun-30 <None> 0 $39.88

You might get a better result if you say —depth 3, the depth of your account of interest. IIRC I improved the aggregation of depth-clipped postings in hledger register.

Best
-Simon

[Erik Hetzner]

Hi Simon,

At Sun, 5 Oct 2014 15:17:32 -1000,

Simon Michael wrote:
>
> Thanks! I have questions:
>
> On Oct 2, 2014, at 7:18 PM, Erik Hetzner <ehet...@gmail.com> wrote:
> > Sure, here Is an example. This is one expense account:
> >
> > $ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month"
> > $267.64 Expenses:Erik:Bicycle
> >
> > That is my total bike spending in 2014.
>
> I'm confused by the "monthly" - my guess it is has no effect in these ledger balance commands ?

Whoops! Yes, it seems to be completely meaningless. :)

> > The number I am looking for is
> > $267.64 / 9 => $29.74, my average monthly spending in 2014 (until
> > Sept) on bike expenses. So this command gives me what I am looking
> > for:
> >
> > $ ledger bal ^Expenses:Erik:Bicycle -E -p "monthly from 2014 until last month" --display-total "(display_total>0 or display_total<0) ? display_total/(to_int(format_datetime(d, '%m')) - 1) : ''"
> > $29.74 Expenses:Erik:Bicycle
>
> Nifty, could you explain the divisor expression a little more ? I don't quite understand how it's working.

Sure, no problem. The first check (display_total>0 or display_total<0)
is to avoid divide by zero. format_datetime(d, '%m') returns the
current month, e.g. 10 for October. to_int makes it an int, - 1 yields
9. So ledger divides the total by 9, the total months we are adding
up.

It's a total hack, as you can see. It only works because we are
starting at the beginning of the year and going to last month. You
could of course pass in the divisor manually and avoid all the hacky
magic.

> > Moreover, if I don't restrict it to that one account, it works for all
> > accounts, giving me my average monthly spending in each expense
> > category for the year.
> >
> > Your command gives me:
> >
> > $ hledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
> > 2014/01/01 - 2014/01/31 Expenses:Erik $22.00 $22.00
> > 2014/02/01 - 2014/02/28 Expenses:Erik $23.07 $22.54
> > 2014/03/01 - 2014/03/31 Expenses:Erik $37.24 $27.44
> > 2014/04/01 - 2014/04/30 Expenses:Erik $157.00 $59.83
> > 2014/05/01 - 2014/05/31 0 $47.86
> > 2014/06/01 - 2014/06/30 0 $39.89
> > 2014/07/01 - 2014/07/31 Expenses:Erik $28.33 $38.23
> > 2014/08/01 - 2014/08/31 0 $33.46
> > 2014/09/01 - 2014/09/30 0 $29.74
> >
> > which does give me the right answer at the end ($29.74) but outputs a
> > lot more and only works on one account.
>
> Right, I forgot to mention "8. the average you want is the last number in the running total column".

No problem! Thanks for the clue on how to do this without that awful
output display hack.

> > ledger doesn't seem to work properly:
> >
> > $ ledger reg -MAE '^expenses:erik:bicycle$' --depth 2 -p "monthly from 2014 until last month"
> > 14-May-01 - 14-May-31 <None> 0 $47.86
> > 14-Jun-01 - 14-Jun-30 <None> 0 $39.88
>

> You might get a better result if you say --depth 3, the depth of your account of interest. IIRC I improved the aggregation of depth-clipped postings in hledger register.

Good point, though I still get the wrong answer:

$ ledger reg -MAE '^expenses:erik:bicycle$' -p "from 2014 until last month" -f finances/ledger.lgr
14-Jan-01 - 14-Jan-31 Expenses:Erik:Bicycle $22.00 $22.00
14-Feb-01 - 14-Feb-28 Expenses:Erik:Bicycle $23.07 $22.54
14-Mar-01 - 14-Mar-31 Expenses:Erik:Bicycle $37.24 $27.44
14-Apr-01 - 14-Apr-30 Expenses:Erik:Bicycle $157.00 $59.83

14-May-01 - 14-May-31 <None> 0 $47.86
14-Jun-01 - 14-Jun-30 <None> 0 $39.88

14-Jul-01 - 14-Jul-31 Expenses:Erik:Bicycle $28.33 $38.23

This seems to be because I had no bike related expenses in the last 2
months of the period (August and September), so the average is not
taking those months into account.

[Simon Michael]

On Oct 6, 2014, at 12:49 PM, Erik Hetzner <ehet...@gmail.com> wrote:

Good point, though I still get the wrong answer:

 $ ledger reg -MAE '^expenses:erik:bicycle$' -p "from 2014 until last month" -f finances/ledger.lgr 
 14-Jan-01 - 14-Jan-31           Expenses:Erik:Bicycle        $22.00       $22.00
 14-Feb-01 - 14-Feb-28           Expenses:Erik:Bicycle        $23.07       $22.54
 14-Mar-01 - 14-Mar-31           Expenses:Erik:Bicycle        $37.24       $27.44
 14-Apr-01 - 14-Apr-30           Expenses:Erik:Bicycle       $157.00       $59.83
 14-May-01 - 14-May-31           <None>                            0       $47.86
 14-Jun-01 - 14-Jun-30           <None>                            0       $39.88
 14-Jul-01 - 14-Jul-31           Expenses:Erik:Bicycle        $28.33       $38.23

This seems to be because I had no bike related expenses in the last 2
months of the period (August and September), so the average is not
taking those months into account.

Ah, another h/ledger difference. With -E, hledger register -M includes leading/trailing empty months, as well as empty months in the middle.

Thanks Erik. I think we can conclude there’s still room for improvement in reporting averages, in ledger and hledger.