emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* monthly budget with year summary using tables
@ 2013-01-02 19:21 Carl Bolduc
  2013-01-03 14:57 ` Darlan Cavalcante Moreira
  0 siblings, 1 reply; 4+ messages in thread
From: Carl Bolduc @ 2013-01-02 19:21 UTC (permalink / raw)
  To: emacs-orgmode

[-- Attachment #1: Type: text/plain, Size: 899 bytes --]

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

[-- Attachment #2: Type: text/html, Size: 1352 bytes --]

^ permalink raw reply	[flat|nested] 4+ messages in thread

* Re: monthly budget with year summary using tables
  2013-01-02 19:21 monthly budget with year summary using tables Carl Bolduc
@ 2013-01-03 14:57 ` Darlan Cavalcante Moreira
  2013-01-03 15:23   ` Stelian Iancu
  0 siblings, 1 reply; 4+ messages in thread
From: Darlan Cavalcante Moreira @ 2013-01-03 14:57 UTC (permalink / raw)
  To: Carl Bolduc; +Cc: emacs-orgmode


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

^ permalink raw reply	[flat|nested] 4+ messages in thread

* Re: monthly budget with year summary using tables
  2013-01-03 14:57 ` Darlan Cavalcante Moreira
@ 2013-01-03 15:23   ` Stelian Iancu
  2013-01-04 17:23     ` Darlan Cavalcante Moreira
  0 siblings, 1 reply; 4+ messages in thread
From: Stelian Iancu @ 2013-01-03 15:23 UTC (permalink / raw)
  To: Darlan Cavalcante Moreira; +Cc: emacs-orgmode, Carl Bolduc

[-- Attachment #1: Type: text/plain, Size: 4385 bytes --]

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 <darcamo@gmail.com
> 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
>
>

[-- Attachment #2: Type: text/html, Size: 5259 bytes --]

^ permalink raw reply	[flat|nested] 4+ messages in thread

* Re: monthly budget with year summary using tables
  2013-01-03 15:23   ` Stelian Iancu
@ 2013-01-04 17:23     ` Darlan Cavalcante Moreira
  0 siblings, 0 replies; 4+ messages in thread
From: Darlan Cavalcante Moreira @ 2013-01-04 17:23 UTC (permalink / raw)
  To: Stelian Iancu; +Cc: emacs-orgmode, Carl Bolduc


The templates I mentioned is not an org-mode feature. I use the excellent
yasnippets for that. In my case, I created a "finanças" file (finances in
Portuguese) in the ~/.emacs.d/snippets/org-mode/ folder (my main snippets
folder). Then, in my main org-mode file I put the cursor in the proper
place, type "finanças" and press TAB. The template is expanded and I can
start adding whatever I want. Because the template is in the "org-mode"
sub-folder of may snippets folder, it will only expand in org-mode buffers.

The only caveat is that since my template includes table formulas, which
have "$", I have to escape these with a backslash because the "$" symbols
has a special meaning in yasnippets templates. Also, in order to get the
current month and year (used to create unique names for the tables as well
as IDs for each category headline) I have the following elisp functions in
my Emacs configuration file

--8<---------------cut here---------------start------------->8---
(defun get-current-year()
  (interactive)
  (format-time-string "%Y" (current-time))
  )

(defun get-current-month()
  (interactive)
  (capitalize (format-time-string "%B" (current-time)))
  )
--8<---------------cut here---------------end--------------->8---

I'll put the whole yasnippets template at the end of this e-mail but
org-mode provides us with such a high flexibility that each person has its
own taste on how thinks should be organized. Maybe you should first create
the org-mode structure for this the way you want and then turn it into a
template for easily replication. It will take a couple or trial and error
to get it right, but you will fell at home with it.

--
Darlan


The template is included below
--8<---------------cut here---------------start------------->8---
# -*- mode: snippet -*-
#name : finaças
# contributor: Darlan Cavalcante Moreira
# --
***** ${1:`(get-current-month)`} ${2:`(get-current-year)`}$0
******* Alimentação
        :PROPERTIES:
        :ID:       $1$2Alimentação
        :END:
        Gastos com Alimentação em dinheiro ou em conta corrente. Não inclui
        gastos com alimentação no cartão de crédito.
        #+TBLNAME: Alimentacao$1$2
        |   | *Local* | *Data*  | *Débito (R\$)* | *Cartão (R\$)* |
        |---+---------+---------+---------------+---------------|
        |   | Nada    |         |          0.00 |          0.00 |
        |---+---------+---------+---------------+---------------|
        | # |         | *Total* |          0.00 |          0.00 |
        #+TBLFM: @3\$4=vsum(@2..@-1);%.2f::@3\$5=vsum(@2..@-1);%.2f

******* Pagamento de contas
        :PROPERTIES:
        :ID:       $1$2Contas
        :END:
        Aqui entram fatura do cartão do mês passado, plano de saúde, Ateneu,
        etc..
        #+TBLNAME: Contas$1$2
        |   | *Conta*       | *Data* | *Débito (R\$)* | *Cartão (R\$)* |
        |---+---------------+--------+---------------+---------------|
        |   | GEAP          |        |          0.00 |          0.00 |
        |   | Celular       |        |          0.00 |          0.00 |
        |   | Ateneu        |        |          0.00 |          0.00 |
        |   | Fatura Cartão |        |               | xxxxxxxxxxxxx |
        |---+---------------+--------+---------------+---------------|
        | # | *Total*       |        |          0.00 |          0.00 |
        #+TBLFM: @6\$4=vsum(@2\$4..@-1);%.2f::@6\$5=vsum(@2\$5..@-2);%.2f

******* Entretenimento
        :PROPERTIES:
        :ID:       $1$2Entretenimento
        :END:
        #+TBLNAME: Entretenimento$1$2
        |   | *Tipo de Gasto* | *Data* | *Débito (R\$)* | *Cartão (R\$)* |
        |---+-----------------+--------+---------------+---------------|
        |   | Entretenimento  |        |          0.00 |          0.00 |
        |---+-----------------+--------+---------------+---------------|
        | # | *Total*         |        |          0.00 |          0.00 |
        #+TBLFM: @3\$4=vsum(@2..@-1);%.2f::@3\$5=vsum(@2..@-1);%.2f

******* Carro e Transporte
        :PROPERTIES:
        :ID:       $1$2Carro
        :END:
        #+TBLNAME: Carro$1$2
        |   | *Tipo de Gasto* | *Data* | *Débito(R\$)* | *Cartão (R\$)* |
        |---+-----------------+--------+--------------+---------------|
        |   | Gasolina        |        |         0.00 |          0.00 |
        |   | Taxi            |        |         0.00 |          0.00 |
        |---+-----------------+--------+--------------+---------------|
        | # | *Total*         |        |         0.00 |          0.00 |
        #+TBLFM: @4\$4=vsum(@2..@-1);%.2f::@4\$5=vsum(@2..@-1);%.2f

******* Diversos
        :PROPERTIES:
        :ID:       $1$2Diversos
        :END:
        Gastos diversos
        #+TBLNAME: Diversos$1$2
        |   | *Tipo de Gasto* | *Data* | *Débito (R\$)* | *Cartão (R\$)* |
        |---+-----------------+--------+---------------+---------------|
        |   | Nada            |        |          0.00 |          0.00 |
        |---+-----------------+--------+---------------+---------------|
        | # | *Total*         |        |          0.00 |          0.00 |
        #+TBLFM: @3\$4=vsum(@2..@-1);%.2f::@3\$5=vsum(@2..@-1);%.2f

******* Total de gastos
        :PROPERTIES:
        :ID:       $1$2GastosTotal
        :END:
        Aqui é colocado o total de cada uma das tabelas anteriores e quaisquer
        outros gastos que não entraram nessas tabelas.
#+TBLNAME: Gastos$1$2
#+PLOT: ind:2 deps:(4) with:histograms
        |   | *Tipo de Gasto*        | *Débito (R\$)* | *Cartão (R\$)* |
        |---+------------------------+---------------+---------------|
        | # | Entretenimento         |               |               |
        | # | Contas                 |               |               |
        | # | Alimentação            |               |               |
        | # | Carro                  |               |               |
        | # | Diversos               |               |               |
        |---+------------------------+---------------+---------------|
        | # | *Total*                |               |               |
        | # | Total + Crédito (Nov.) |               |               |
        #+TBLFM: @2\$3=remote(Entretenimento$1$2,\$LR4);%.2f::@2\$4=remote(Entretenimento$1$2,\$LR5);%.2f::@3\$3=remote(Contas$1$2,\$LR4);%.2f::@3\$4=remote(Contas$1$2,\$LR5);%.2f::@4\$3=remote(Alimentacao$1$2,\$LR4);%.2f::@4\$4=remote(Alimentacao$1$2,\$LR5);%.2f::@5\$3=remote(Carro$1$2,\$LR4);%.2f::@5\$4=remote(Carro$1$2,\$LR5);%.2f::@6\$3=remote(Diversos$1$2,\$LR4);%.2f::@6\$4=remote(Diversos$1$2,\$LR5);%.2f::@7\$3=vsum(@2..@-1);%.2f::@7\$4=vsum(@2..@-1);%.2f::@8\$3=@-1\$3+@-1\$4;%.2f
        #+Obs.: A fórmula Total + Crédito diz quanto gastei em $1 incluindo o
        #+gasto do cartão de crédito (sem incluir o gasto em dólares no cartão)

        #+BEGIN_SRC gnuplot :var data=Gastos$1$2[1:-3] :var soma=Gastos$1$2[9,2] :results silent :exports none
          reset
          set key off
          set title "Gastos de $1 $2"
          set xtics nomirror rotate by -10
          plot data using 3:xticlabels(2) with histograms
          set label "Máximo Individual: %.2f",GPVAL_Y2_MAX at graph 0.03, graph 0.93
          set label "Total: %.2f",soma at graph 0.03, graph 0.88 textcolor rgbcolor "red"
          replot
        #+END_SRC

--8<---------------cut here---------------end--------------->8---



At Thu, 3 Jan 2013 16:23:34 +0100,
Stelian Iancu wrote:
> 
> [1  <text/plain; ISO-8859-1 (7bit)>]
> 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 <darcamo@gmail.com
> > 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
> >
> >
> [2  <text/html; ISO-8859-1 (quoted-printable)>]
> 

^ permalink raw reply	[flat|nested] 4+ messages in thread

end of thread, other threads:[~2013-01-04 17:39 UTC | newest]

Thread overview: 4+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2013-01-02 19:21 monthly budget with year summary using tables Carl Bolduc
2013-01-03 14:57 ` Darlan Cavalcante Moreira
2013-01-03 15:23   ` Stelian Iancu
2013-01-04 17:23     ` Darlan Cavalcante Moreira

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).