emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Darlan Cavalcante Moreira <darcamo@gmail.com>
To: Stelian Iancu <lists@siancu.net>
Cc: "emacs-orgmode@gnu.org" <emacs-orgmode@gnu.org>,
	Carl Bolduc <carlbolduc@gmail.com>
Subject: Re: monthly budget with year summary using tables
Date: Fri, 04 Jan 2013 14:23:31 -0300	[thread overview]
Message-ID: <50e71017.6945ec0a.68c0.ffffca08@mx.google.com> (raw)
In-Reply-To: <CAGoVJLGeTfGYiV8-oYwmhOJE58j+D4SotQK+eOyw0dDxXvq3yA@mail.gmail.com>


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)>]
> 

      reply	other threads:[~2013-01-04 17:39 UTC|newest]

Thread overview: 4+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
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 [this message]

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=50e71017.6945ec0a.68c0.ffffca08@mx.google.com \
    --to=darcamo@gmail.com \
    --cc=carlbolduc@gmail.com \
    --cc=emacs-orgmode@gnu.org \
    --cc=lists@siancu.net \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
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).