emacs-orgmode
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Question Regarding Creating Workflow For Automatic Formulas For Fina


From: John Hendy
Subject: Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet
Date: Sun, 9 Jan 2022 13:48:27 -0600

On Sun, Jan 9, 2022 at 11:17 AM Samuel Banya <sbanya@fastmail.com> wrote:
>
> Hey there,
>

[...]

> After Reading The Online Manual, I Figured This Out:
>
> I tried using the 'org-table-edit-formulas' function via the "C-c ' (single 
> quote)" (Obtained this formula via the docs, 
> 'https://www.gnu.org/software/emacs/manual/html_mono/org.html#Formula-syntax-for-Lisp')
> I then accepted the mini buffer's changes with 'C-c C-c' which goes back to 
> the spreadsheet.
> I then hit 'C-c *' to update the tables present.
> The problem with this is that when I attempt to do this function for each 
> separate calculation row, both of the bottom calculation rows now only refer 
> to a single formula for some reason.
>

>From using org table formulas, I think the problem is that to my
knowledge, there is no way to maintain multiple formulas for a column.

"""
When you assign a formula to a simple column reference like ‘$3=’, the
same formula is used in all fields of that column
"""
https://orgmode.org/manual/Column-formulas.html

>
> For Reference, Here's A Modified Version Of My Running Finance Spreadsheet

[...]

> Why can't I use individual row formulas in this scenario?
> Why is it that when I use "C-c ' (single quote)" the row formulas are the 
> same for completely separate rows

After you enter these formulas, do you see the line that shows up
below the table? They were omitted in the sample spreadsheet above,
but for me, they look like this:

#+TBLFM: $3=vsum(@1$3..@2$3)::$4=vsum(@1$4..@2$4)

To my knowledge, Org-mode has no mechanism for what you really want,
per *cell* formulas. You're using per *column* formulas, and the
notation matches: for column 4, the formula is foo. There is no
differentiation for rows a and b, just that all of this column will
calculate foo.

> My Main Workflow Questions Include The Following:
>
> Is there a way I can maybe automate the initial steps of converting that .csv 
> into an .org mode doc, and to paste them into the existing org spreadsheet?

Probably, but without full knowledge of what you exactly want/need,
it's hard to comment further. For example, in theory you could just
open the .csv in emacs directly and:

`M-x replace-string [RET] , [RET] |

That would get you at least partially there. Do you need the .csv in
org-mode for some reason? Maybe the answer is "yes" and thus it
justifies solving this aspect further. Maybe the answer is "not
really, I just care about the totals" in which case this is a bit of a
tangent.

> Is there a way I can also update the existing formula for the given month if 
> Emacs would somehow know the current date time stamp and figure out the 
> month's row at the bottom of the spreadsheet accordingly to update the 
> correct row?

I think the reason ledger was suggested (a plain text finance program
with a mode for emacs) is that the answer is probably "no, this is
getting a bit complicated for org spreadsheets and calculations
directly."

I might suggest learning a little bit of python or R. Probably sounds
daunting, but I think it would be easier to pick up at least as much
as you've already taught yourself with respect to org calculation
field syntax! Here's an example:

#+begin_example
* foo

#+name: foo
|------------+-----------------------------------------------------+---+----------|
| 01/03/2022 | Example Rent Expense                                |
| -1061.67 |
| 01/04/2022 | Example Food Expense                                |
| -1061.67 |
| 02/05/2022 | Example Utility Expense                             |
| -2061.67 |
| 02/06/2022 | Example Random Expense                              |
| -2061.67 |
|------------+-----------------------------------------------------+---+----------|
#+TBLFM: $3=vsum(@1$3..@2$3)::$4=vsum(@1$4..@2$4)

#+begin_src R :var foo=foo
jan <- sum(foo[1:2, 4])
feb <- sum(foo[3:4, 4])

result <- data.frame(
  month = c("jan", "feb"),
  total = c(jan, feb))

print(result)
#+end_src

#+RESULTS:
| jan | -2123.34 |
| feb | -4123.34 |
#+end_example

R can read in org tables, so that could merge these two solutions. R
can *also* just read in .csv files... so you'd not need to monkey with
.csv -> org at all. As mentioned, teaching yourself @1$4..@2$4 isn't
that much different from [1:2, 4] in R :)

Plus, then you absolutely have date manipulation available, either in
native R or with packages like lubridate which could read in your data
source date syntax (you'd tell it that the date was in "%m/%d/%Y"
format, and then filter to month == 1 for January.

> Anyway, I know my question might be a bit ambiguous and most likely will 
> involve using 'F3' to record macros, but I figured i would ask to maybe make 
> it easier since one of my goals for the new year is to make recording 
> finances an easier process.
>
> I felt like I've been over complicating this, and figured someone probably is 
> doing org finance spreadsheets better than me to figure this out.

Hopefully some useful ideas above. Sorry that I don't have a better
answer to specifically where you were hoping to take this solution.

Best regards,
John


> Sincerely,
>
> Sam
>



reply via email to

[Prev in Thread] Current Thread [Next in Thread]