From mboxrd@z Thu Jan 1 00:00:00 1970 From: Darlan Cavalcante Moreira Subject: Re: monthly budget with year summary using tables Date: Fri, 04 Jan 2013 14:23:31 -0300 Message-ID: <50e71017.6945ec0a.68c0.ffffca08@mx.google.com> References: <50e59c56.0e6d650a.67a9.ffffb53c@mx.google.com> Mime-Version: 1.0 (generated by SEMI 1.14.6 - "Maruoka") Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Return-path: Received: from eggs.gnu.org ([208.118.235.92]:33054) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TrBFH-0005MV-23 for emacs-orgmode@gnu.org; Fri, 04 Jan 2013 12:39:53 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1TrBFE-0008Uu-Qc for emacs-orgmode@gnu.org; Fri, 04 Jan 2013 12:39:50 -0500 Received: from mail-ye0-f181.google.com ([209.85.213.181]:34911) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TrAzZ-0004O9-6Z for emacs-orgmode@gnu.org; Fri, 04 Jan 2013 12:23:37 -0500 Received: by mail-ye0-f181.google.com with SMTP id m11so2595123yen.26 for ; Fri, 04 Jan 2013 09:23:36 -0800 (PST) In-Reply-To: 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: 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 ] > 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 > > > > > [2 ] >