emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: John Hendy <jw.hendy@gmail.com>
To: Samuel Banya <sbanya@fastmail.com>
Cc: Charles Berry <emacs-orgmode@gnu.org>
Subject: Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet
Date: Sun, 9 Jan 2022 13:48:27 -0600	[thread overview]
Message-ID: <CA+M2ft_SEyyR+cwrvVy_g7pH2yUm7zOTTBV+ZBHxPgLs=MpETA@mail.gmail.com> (raw)
In-Reply-To: <137562d1-e6fe-4a68-b18b-abedef513fbd@www.fastmail.com>

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
>


  parent reply	other threads:[~2022-01-09 19:49 UTC|newest]

Thread overview: 25+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2022-01-09 16:56 Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet Samuel Banya
2022-01-09 19:05 ` Ken Mankoff
2022-01-09 19:48 ` John Hendy [this message]
2022-01-09 22:37 ` Neil Jerram
2022-01-10  3:42   ` Samuel Banya
2022-01-10  7:04     ` Marcin Borkowski
2022-01-10  7:17       ` Detlef Steuer
2022-01-10 15:31       ` Greg Minshall
2022-01-10 17:01         ` Samuel Banya
2022-01-10 17:23           ` John Hendy
2022-01-10 19:04           ` Marcin Borkowski
2022-01-10 22:44             ` Samuel Wales
2022-01-11  0:30             ` Samuel Banya
2022-01-11  0:41               ` John Hendy
2022-01-11  0:43                 ` Samuel Banya
2022-01-10 10:31     ` Neil Jerram
2022-01-19 15:01       ` Neil Jerram
2022-01-14 19:54     ` Quiliro Ordóñez
2022-01-16 15:15       ` Samuel Banya
2022-01-16 15:19         ` Samuel Banya
2022-01-16 15:35           ` Samuel Banya
2022-01-16 18:10             ` Eric S Fraga
2022-01-17  0:41           ` John Hendy
2022-01-10 13:35 ` Eric S Fraga
2022-01-10 17:09   ` Bob Newell

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='CA+M2ft_SEyyR+cwrvVy_g7pH2yUm7zOTTBV+ZBHxPgLs=MpETA@mail.gmail.com' \
    --to=jw.hendy@gmail.com \
    --cc=emacs-orgmode@gnu.org \
    --cc=sbanya@fastmail.com \
    /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).