From mboxrd@z Thu Jan 1 00:00:00 1970 From: Stelian Iancu Subject: Re: monthly budget with year summary using tables Date: Thu, 3 Jan 2013 16:23:34 +0100 Message-ID: References: <50e59c56.0e6d650a.67a9.ffffb53c@mx.google.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=e89a8ff1c8b83befcc04d263f2a9 Return-path: Received: from eggs.gnu.org ([208.118.235.92]:57029) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Tqmdy-0002VA-1d for emacs-orgmode@gnu.org; Thu, 03 Jan 2013 10:23:45 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1Tqmdu-0006ZR-J2 for emacs-orgmode@gnu.org; Thu, 03 Jan 2013 10:23:41 -0500 Received: from mail-pa0-f50.google.com ([209.85.220.50]:46346) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Tqmdu-0006ZF-A2 for emacs-orgmode@gnu.org; Thu, 03 Jan 2013 10:23:38 -0500 Received: by mail-pa0-f50.google.com with SMTP id hz10so8683351pad.37 for ; Thu, 03 Jan 2013 07:23:37 -0800 (PST) In-Reply-To: <50e59c56.0e6d650a.67a9.ffffb53c@mx.google.com> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Darlan Cavalcante Moreira Cc: "emacs-orgmode@gnu.org" , Carl Bolduc --e89a8ff1c8b83befcc04d263f2a9 Content-Type: text/plain; charset=ISO-8859-1 This is fascinating, thanks for explaining this. One question though is about the templates. I wasn't aware you can have such things in org-mode. Can you elaborate a bit on those? Thanks! S. On Thu, Jan 3, 2013 at 3:57 PM, Darlan Cavalcante Moreira wrote: > > A have a setup similar to the one you ask, using tables, but I don't need > babel for that. > > Basically, I have a "Finances" headline in my main org file, whose children > are "years" headlines. In the beginning of each month I create a new > headline for that month in the current year using a template so that I have > something similar to > > * Finances > ... > * 2013 > * January > * Food > * Bills > * Entertainment > * Car and transportation > * Misc > * Total > > Each of the category has a table where I put the expenses for that category > and each table computes its total value. For instance, the table in the > "Food" category is something like > > #+TBLNAME: FoodJanuary2013 > | | *Place* | *Date* | *Debit (R$)* | *Card (R$)* | > |---+------------+------------------+--------------+-------------| > | | Some place | [2013-01-03 Qui] | 10.00 | 0.00 | > |---+------------+------------------+--------------+-------------| > | # | | *Total* | 10.00 | 0.00 | > #+TBLFM: @3$4=vsum(@2..@-1);%.2f::@3$5=vsum(@2..@-1);%.2f > > where I add the expense either to the Debit column (when I pay with cash or > my debit card) or to the Card column (when I pay with the credit card). > > Notice that the table has a unique name with the category, month and year > (all of this is created automatically with a template). In the "Total" > category I have a table that uses remote references to get the total value > of each category. This table gives me a glimpse of my expenses so far (I > can even plot it to see how much I spent in each category, for instance, > this is a good use of babel). This table is similar to > > #+TBLNAME: ExpensesJanuary2013 > | | *Category* | *Debit (R$)* | *Card (R$)* | > |---+------------------------+---------------+---------------| > | # | Entertainment | | | > | # | Bills | | | > | # | Food | | | > | # | Car and transportation | | | > | # | Misc | | | > |---+------------------------+---------------+---------------| > | # | *Total* | | | > | # | Total + Credit | | | > #+TBLFM: @2$3=remote(EntertainimentJaneiro2013,$LR4);%.2f::@2 > $4=remote(EntertainimentJaneiro2013,$LR5);%.2f::@3 > $3=remote(BillsJaneiro2013,$LR4);%.2f::@3 > $4=remote(BillsJaneiro2013,$LR5);%.2f::@4 > $3=remote(FoodJaneiro2013,$LR4);%.2f::@4 > $4=remote(FoodJaneiro2013,$LR5);%.2f::@5 > $3=remote(CarJaneiro2013,$LR4);%.2f::@5 > $4=remote(CarJaneiro2013,$LR5);%.2f::@6 > $3=remote(MiscJaneiro2013,$LR4);%.2f::@6 > $4=remote(MiscJaneiro2013,$LR5);%.2f::@7$3=vsum(@2..@-1);%.2f::@7 > $4=vsum(@2..@-1);%.2f::@8$3=@-1$3+@-1$4;%.2f > > In the end, org can do what you want the way you want. > > -- > Darlan > > > At Wed, 2 Jan 2013 14:21:12 -0500, > Carl Bolduc wrote: > > > > I would like to move my budget from MS Excel to Org-Mode. I currently > have > > one Excel sheet per month with all the transactions as well as a summary > > sheet that uses sumif functions. > > > > Assuming the following format for the monthly table: > > > > #+tblname: january > > | date | transaction | amount | category | > > |------+-------------+--------+-----------| > > | 01 | iga | 50 | groceries | > > | 01 | echo | 30 | car | > > | 02 | iga | 47 | groceries | > > > > How can I get the following summary table? > > > > | category | january | february | > > |-----------+---------+----------| > > | groceries | 97 | | > > | car | 30 | | > > > > I found this post on the mailing list that suggest to use babel: > > http://www.mail-archive.com/emacs-orgmode@gnu.org/msg22736.html > > > > However, I need help to understand how I can put the resulting sums > inside > > the summary table. > > > > Thanks, > > Carl > > --e89a8ff1c8b83befcc04d263f2a9 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
This is fascinating, thanks for explaining this. One quest= ion though is about the templates. I wasn't aware you can have such thi= ngs in org-mode. Can you elaborate a bit on those?

Thank= s!

S.


On Thu, Jan 3, 2013 at 3:57 PM, Darlan Cavalcante Moreira <darcamo@gmail= .com> wrote:

A have a setup similar to the one you ask, using tables, but I don't ne= ed
babel for that.

Basically, I have a "Finances" headline in my main org file, whos= e children
are "years" headlines. In the beginning of each month I create a = new
headline for that month in the current year using a template so that I have=
something similar to

* Finances
=A0 ...
=A0 * 2013
=A0 =A0 * January
=A0 =A0 =A0 * Food
=A0 =A0 =A0 * Bills
=A0 =A0 =A0 * Entertainment
=A0 =A0 =A0 * Car and transportation
=A0 =A0 =A0 * Misc
=A0 =A0 =A0 * Total

Each of the category has a table where I put the expenses for that category=
and each table computes its total value. For instance, the table in the
"Food" category is something like

#+TBLNAME: FoodJanuary2013
| =A0 | *Place* =A0 =A0| *Date* =A0 =A0 =A0 =A0 =A0 | *Debit (R$)* | *Card = (R$)* |
|---+------------+------------------+--------------+-------------|
| =A0 | Some place | [2013-01-03 Qui] | =A0 =A0 =A0 =A010.00 | =A0 =A0 =A0 = =A00.00 |
|---+------------+------------------+--------------+-------------|
| # | =A0 =A0 =A0 =A0 =A0 =A0| *Total* =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0= 10.00 | =A0 =A0 =A0 =A00.00 |
#+TBLFM: @3$4=3Dvsum(@2..@-1);%.2f::@3$5=3Dvsum(@2..@-1);%.2f

where I add the expense either to the Debit column (when I pay with cash or=
my debit card) or to the Card column (when I pay with the credit card).

Notice that the table has a unique name with the category, month and year (all of this is created automatically with a template). In the "Total&= quot;
category I have a table that uses remote references to get the total value<= br> of each category. This table gives me a glimpse of my expenses so far (I can even plot it to see how much I spent in each category, for instance, this is a good use of babel). This table is similar to

#+TBLNAME: ExpensesJanuary2013
| =A0 | *Category* =A0 =A0 =A0 =A0 =A0 =A0 | *Debit (R$)* =A0| *Card (R$)* = =A0 |
|---+------------------------+---------------+---------------|
| # | Entertainment =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 = =A0 =A0 =A0 =A0 =A0 =A0 |
| # | Bills =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 = =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
| # | Food =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 = =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
| # | Car and transportation | =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 = =A0 =A0 =A0 =A0 |
| # | Misc =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 = =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
|---+------------------------+---------------+---------------|
| # | *Total* =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 = | =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
| # | Total + Credit =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 = =A0 =A0 =A0 =A0 =A0 =A0 |
#+TBLFM: @2$3=3Dremote(EntertainimentJaneiro2013,$LR4);%.2f::@2$4=3Dremote(= EntertainimentJaneiro2013,$LR5);%.2f::@3$3=3Dremote(BillsJaneiro2013,$LR4);= %.2f::@3$4=3Dremote(BillsJaneiro2013,$LR5);%.2f::@4$3=3Dremote(FoodJaneiro2= 013,$LR4);%.2f::@4$4=3Dremote(FoodJaneiro2013,$LR5);%.2f::@5$3=3Dremote(Car= Janeiro2013,$LR4);%.2f::@5$4=3Dremote(CarJaneiro2013,$LR5);%.2f::@6$3=3Drem= ote(MiscJaneiro2013,$LR4);%.2f::@6$4=3Dremote(MiscJaneiro2013,$LR5);%.2f::@= 7$3=3Dvsum(@2..@-1);%.2f::@7$4=3Dvsum(@2..@-1);%.2f::@8$3=3D@-1$3+@-1$4;%.2= f

In the end, org can do what you want the way you want.

--
Darlan


At Wed, 2 Jan 2013 14:21:12 -0500,
Carl Bolduc wrote:
>
> I would like to move my budget from MS Excel to Org-Mode. I currently = have
> one Excel sheet per month with all the transactions as well as a summa= ry
> sheet that uses sumif functions.
>
> Assuming the following format for the monthly table:
>
> #+tblname: january
> | date | transaction | amount | category =A0|
> |------+-------------+--------+-----------|
> | =A0 01 | iga =A0 =A0 =A0 =A0 | =A0 =A0 50 | groceries |
> | =A0 01 | echo =A0 =A0 =A0 =A0| =A0 =A0 30 | car =A0 =A0 =A0 |
> | =A0 02 | iga =A0 =A0 =A0 =A0 | =A0 =A0 47 | groceries |
>
> How can I get the following summary table?
>
> | category =A0| january | february |
> |-----------+---------+----------|
> | groceries | =A0 =A0 =A097 | =A0 =A0 =A0 =A0 =A0|
> | car =A0 =A0 =A0 | =A0 =A0 =A030 | =A0 =A0 =A0 =A0 =A0|
>
> I found this post on the mailing list that suggest to use babel:
> http://www.mail-archive.com/emacs-orgmode@gnu.org/m= sg22736.html
>
> However, I need help to understand how I can put the resulting sums in= side
> the summary table.
>
> Thanks,
> Carl


--e89a8ff1c8b83befcc04d263f2a9--