* 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@gnu.org, 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