Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 4671 bytes --]

Hey there,

So I've been managing my finances via an org doc that basically has tables that list all the expenses from my bank account, which has been awesome on some respects.

The only annoying thing I want to somehow figure out this year is how to make the process of updating the formula for a given month less tedious, so I am wondering if anyone could help me figure out a better workflow for doing this.
*
*
*Here's My Current Workflow*
 * Download .csv from banking website
 * Convert .csv to .org file via file manager (with 'ranger' in 'vterm')
 * Change over to Dired Mode in the same directory and view the same .org file, and convert the data into an org table by using 'C-x h' to highlight everything, and use 'C-c |' to convert the range into an org table
 * Open up the existing running total org mode spreadsheet in a separate buffer
 * Copy over the converted org mode table data from the new .csv buffer and paste it into the running org mode spreadsheet
 * Use 'C-c }' to show all formulas row and column values in the spreadsheet
 * Find the existing formula row line for the given month's totals at the bottom of the spreadsheet, and manually delete the values for the 3rd and 4th row respectively
 * Re-enter the formula manually for each cell and hit tab
 * Enter the same formulas on the next line to double check my work to make sure I entered them in correctly.
*
*
*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.

*For Reference, Here's A Modified Version Of My Running Finance Spreadsheet (Modified Without Actual Values Or Records For Personal Reasons) (NOTE: Sorry that the paste is terribly aligned --> I blame email formatting for this, also, note that I also put a note for myself as to what the formulas involved are for a given month since I can never remember the syntax personally since its flipped from typical Excel syntax of using row and column --> it uses column then row like an older calculator)*

|------------+------------------------------------------------------+---+----------|
| 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 |
|------------+------------------------------------------------------+---+----------|
|            | EXPENSES JAN 2022 vsum(@1$3..@2$3) vsum(@1$4..@2$4)  | 0 | -2123.34 |
|            | EXPENSES Feb 2022 vsum(@3$3..@4$3) vsum(@3$4..@4$4)  | 0 | -4123.34 |
|------------+------------------------------------------------------+---+----------|

*Formulas Presented In the 3rd and 4th columns for Row 5:**
*
# Column Formulas
$3 = vsum(@1$3..@2$3)
$4 = vsum(@1$4..@2$4)

*Formulas Presented In The 3rd And 4th Columns For Row 6 (**NOTE**: Notice how I can't make them unique for each row for some reason):*
# Column Formulas
$3 = vsum(@1$3..@2$3)
$4 = vsum(@1$4..@2$4)
*
*
*Main Questions Regarding Formula Issues:*
 * 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

*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?
 * 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?

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.

Sincerely,

Sam

[-- Attachment #2: Type: text/html, Size: 6576 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Ken Mankoff]

[-- Attachment #1: Type: text/plain, Size: 5176 bytes --]

Hi Sam,

Have you looked into Org Ledger?

  -k.

Please excuse brevity. Sent from tiny pocket computer with non-haptic
feedback keyboard.

On Sun, Jan 9, 2022, 09:18 Samuel Banya <sbanya@fastmail.com> wrote:

> Hey there,
>
> So I've been managing my finances via an org doc that basically has tables
> that list all the expenses from my bank account, which has been awesome on
> some respects.
>
> The only annoying thing I want to somehow figure out this year is how to
> make the process of updating the formula for a given month less tedious, so
> I am wondering if anyone could help me figure out a better workflow for
> doing this.
>
> *Here's My Current Workflow*
>
>    - Download .csv from banking website
>    - Convert .csv to .org file via file manager (with 'ranger' in 'vterm')
>    - Change over to Dired Mode in the same directory and view the same
>    .org file, and convert the data into an org table by using 'C-x h' to
>    highlight everything, and use 'C-c |' to convert the range into an org table
>    - Open up the existing running total org mode spreadsheet in a
>    separate buffer
>    - Copy over the converted org mode table data from the new .csv buffer
>    and paste it into the running org mode spreadsheet
>    - Use 'C-c }' to show all formulas row and column values in the
>    spreadsheet
>    - Find the existing formula row line for the given month's totals at
>    the bottom of the spreadsheet, and manually delete the values for the 3rd
>    and 4th row respectively
>    - Re-enter the formula manually for each cell and hit tab
>    - Enter the same formulas on the next line to double check my work to
>    make sure I entered them in correctly.
>
>
> *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.
>
>
> *For Reference, Here's A Modified Version Of My Running Finance
> Spreadsheet (Modified Without Actual Values Or Records For Personal
> Reasons) (NOTE: Sorry that the paste is terribly aligned --> I blame email
> formatting for this, also, note that I also put a note for myself as to
> what the formulas involved are for a given month since I can never remember
> the syntax personally since its flipped from typical Excel syntax of using
> row and column --> it uses column then row like an older calculator)*
>
>
> |------------+------------------------------------------------------+---+----------|
> | 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 |
>
> |------------+------------------------------------------------------+---+----------|
> |            | EXPENSES JAN 2022 vsum(@1$3..@2$3) vsum(@1$4..@2$4)  | 0 |
> -2123.34 |
> |            | EXPENSES Feb 2022 vsum(@3$3..@4$3) vsum(@3$4..@4$4)  | 0 |
> -4123.34 |
>
> |------------+------------------------------------------------------+---+----------|
>
> *Formulas Presented In the 3rd and 4th columns for Row 5:*
> # Column Formulas
> $3 = vsum(@1$3..@2$3)
> $4 = vsum(@1$4..@2$4)
>
> *Formulas Presented In The 3rd And 4th Columns For Row 6 (**NOTE**:
> Notice how I can't make them unique for each row for some reason):*
> # Column Formulas
> $3 = vsum(@1$3..@2$3)
> $4 = vsum(@1$4..@2$4)
>
> *Main Questions Regarding Formula Issues:*
>
>    - 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
>
>
> *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?
>    - 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?
>
>
> 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.
>
> Sincerely,
>
> Sam
>
>

[-- Attachment #2: Type: text/html, Size: 6642 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[John Hendy]

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
>

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Neil Jerram]

[-- Attachment #1: Type: text/plain, Size: 1064 bytes --]

On Sun, 9 Jan 2022, 17:18 Samuel Banya, <sbanya@fastmail.com> wrote:

> Hey there,
>
> So I've been managing my finances via an org doc that basically has tables
> that list all the expenses from my bank account, which has been awesome on
> some respects.
>

I also save and process my bank account transactions with Org, and I agree
that it's awesome.  The bank's own website and analysis capabilities are
rubbish in comparison.

For what it's worth, my approach is:

- Periodically download transactions when available (in OFX format, but
that's not important).

- Use Org and Babel with Scheme code to run arbitrary analyses over those,
with the results displayed in Org tables.

- Sometimes there are cases when I need an additional computation on one of
the output tables, and I can do that with an Org spreadsheet formula.  But
if it was anything useful in the longer term, I'd add the logic to the
Scheme coding instead.

So I'd agree with the response that this kind of thing is a bit too complex
for Org table formulas alone.

Best wishes,
     Neil

>
>

[-- Attachment #2: Type: text/html, Size: 1970 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 1799 bytes --]

Ah, Ledger is way too complicated for what it's worth. I've seen videos on YouTube about it, and every person who's praised it is pretty much too smart enough to be able to explain it in simple terms.

I don't like how manual and frequent it is, and for my use case, I just download .csvs from my bank.

For what I use it for, Org Mode gets close.

I just needed to figure out how I can make those formulas actually work in this scenario using Emacs with Elisp itself.

There's gotta be a way to do this, since its weird how the individual rows don't have individual formulas. 

That's the big weird thing for me personally.

Sincerely,

Sam

On Sun, Jan 9, 2022, at 5:37 PM, Neil Jerram wrote:
> 
> On Sun, 9 Jan 2022, 17:18 Samuel Banya, <sbanya@fastmail.com> wrote:
>> __
>> Hey there,
>> 
>> So I've been managing my finances via an org doc that basically has tables that list all the expenses from my bank account, which has been awesome on some respects.
> 
> I also save and process my bank account transactions with Org, and I agree that it's awesome.  The bank's own website and analysis capabilities are rubbish in comparison.
> 
> For what it's worth, my approach is:
> 
> - Periodically download transactions when available (in OFX format, but that's not important).
> 
> - Use Org and Babel with Scheme code to run arbitrary analyses over those, with the results displayed in Org tables.
> 
> - Sometimes there are cases when I need an additional computation on one of the output tables, and I can do that with an Org spreadsheet formula.  But if it was anything useful in the longer term, I'd add the logic to the Scheme coding instead.
> 
> 
> So I'd agree with the response that this kind of thing is a bit too complex for Org table formulas alone.
> 
> Best wishes,
>      Neil
>> 

[-- Attachment #2: Type: text/html, Size: 3393 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Marcin Borkowski]

On 2022-01-10, at 04:42, Samuel Banya <sbanya@fastmail.com> wrote:

> Ah, Ledger is way too complicated for what it's worth. I've seen videos on YouTube about it, and every person who's praised it is pretty much too smart enough to be able to explain it in simple terms.

Interesting - I've been using Ledger for many years now, and while
I agree that it has its dark corners, it's much like Org in that respect
- you are not forced to use them at all.  And the "core" is IMHO /very/
simple - you just record your "transactions" (e.g., using the very nice
Emacs ledger-mode) and look at a report.

I basically use just one kind of report - the balance report, both to
reconcile my ledger file with my cash and bank and to see the
current/previous month's income and expenses.

Before I started using Ledger, I used GNUcash (on and off for a few
years, too) - I can't say a bad word about it, but it's not Emacs-based,
so you know.  Also, it's not exactly text-based (AFAIR, it uses XML, so
it "kind of" is, but...) - a big advantage of Ledger is that I can keep
my books in Git.  (Of course, using Org would have the same advantage.)

Also, if you don't insist on Ledger, https://plaintextaccounting.org/
has a comparison of other text-based accounting tools.

I'm wondering if your opinion about Ledger isn't influenced by the fact
that it uses basic accounting principles, like the idea of double-entry
accounting.  For me, it's /very/ simple and intuitive, but I graduated
in economics many years ago and had a (basic, but still) course on
accounting.  If that is your problem, I'd suggest spending some time on
understanding that - even if you don't end up using Ledger but some
other software, you might find learning about accounting useful.  (Also,
it's fascinating.  No, really!  It's an extremely clever abstract
system with a real-life use-case.)

That said, there are people who just use a regular spreadsheet for their
personal/household finance, and it seems to work for them.  Go figure.
(Now that I think of it, I'll have to ask someone who does how they
manage that.)

Having said all that, would someone be interested in a blog post(s) with
a Ledger basics crash course?

Hth,

-- 
Marcin Borkowski
http://mbork.pl

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Detlef Steuer]

Am Mon, 10 Jan 2022 08:04:20 +0100
schrieb Marcin Borkowski <mbork@mbork.pl>:

> On 2022-01-10, at 04:42, Samuel Banya <sbanya@fastmail.com> wrote:
> 
> > Ah, Ledger is way too complicated for what it's worth. I've seen
> > videos on YouTube about it, and every person who's praised it is
> > pretty much too smart enough to be able to explain it in simple
> > terms.  
> 
> Interesting - I've been using Ledger for many years now, and while
> I agree that it has its dark corners, it's much like Org in that
> respect
> - you are not forced to use them at all.  And the "core" is IMHO
> /very/ simple - you just record your "transactions" (e.g., using the
> very nice Emacs ledger-mode) and look at a report.
> 
> I basically use just one kind of report - the balance report, both to
> reconcile my ledger file with my cash and bank and to see the
> current/previous month's income and expenses.
> 
> Before I started using Ledger, I used GNUcash (on and off for a few
> years, too) - I can't say a bad word about it, but it's not
> Emacs-based, so you know.  Also, it's not exactly text-based (AFAIR,
> it uses XML, so it "kind of" is, but...) - a big advantage of Ledger
> is that I can keep my books in Git.  (Of course, using Org would have
> the same advantage.)
> 
> Also, if you don't insist on Ledger, https://plaintextaccounting.org/
> has a comparison of other text-based accounting tools.
> 
> I'm wondering if your opinion about Ledger isn't influenced by the
> fact that it uses basic accounting principles, like the idea of
> double-entry accounting.  For me, it's /very/ simple and intuitive,
> but I graduated in economics many years ago and had a (basic, but
> still) course on accounting.  If that is your problem, I'd suggest
> spending some time on understanding that - even if you don't end up
> using Ledger but some other software, you might find learning about
> accounting useful.  (Also, it's fascinating.  No, really!  It's an
> extremely clever abstract system with a real-life use-case.)
> 
> That said, there are people who just use a regular spreadsheet for
> their personal/household finance, and it seems to work for them.  Go
> figure. (Now that I think of it, I'll have to ask someone who does
> how they manage that.)
> 
> Having said all that, would someone be interested in a blog post(s)
> with a Ledger basics crash course?

Yes.

Detlef

> 
> Hth,
> 

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Neil Jerram]

On Mon, 10 Jan 2022 at 03:42, Samuel Banya <sbanya@fastmail.com> wrote:
>
> Ah, Ledger is way too complicated for what it's worth. I've seen videos on YouTube about it, and every person who's praised it is pretty much too smart enough to be able to explain it in simple terms.

I have similar observations.  I don't (so far) see that Ledger (or
alternatives) offers enough added value to be worth incorporating in
my workflow, compared to directly processing the raw data.

The value that would be of interest to me is:
- Classifying/grouping transactions at analysis time (not entry time)
according to a set of rules/regexps etc.
- Drawing insights by analysing past expenditure, with that kind of
classification.
- Using such insights as part of future budgeting.
- An overall functional approach, where the raw data is never changed,
and the other inputs to an analysis can be tweaked on each run.

I wish plaintextaccounting.org had a forum/ML for discussing this kind
of thing, as it isn't specific to any of ledger/hledger/beancount in
particular.

Best wishes,
    Neil

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Eric S Fraga]

I would suggest the following:

|------------+-----------------------------------------------------+---+----------|
| 01/03/2022 | Example Rent Expense                                |   | -1061.67 |
| 01/04/2022 | Example Food Expense                                |   | -1061.67 |
|------------+-----------------------------------------------------+---+----------|
|            | January expenses                                    | 0 | -2123.34 |
|------------+-----------------------------------------------------+---+----------|
| 02/05/2022 | Example Utility Expense                             |   | -2061.67 |
| 02/06/2022 | Example Random Expense                              |   | -2061.67 |
|------------+-----------------------------------------------------+---+----------|
|            | February expenses                                   | 0 | -4123.34 |
|------------+-----------------------------------------------------+---+----------|
#+TBLFM: @3$3=vsum(@-I..@-II)::@3$4=vsum(@-I..@-II)::@6$3=vsum(@-I..@-II)::@6$4=vsum(@-I..@-II)

where each month is collated separately; all you have to do is append
each month to the table, add some horizontal lines and copy the formulas
modifying the row number.

But I also would highly recommend ledger which is what I use for my
accounting and which integrates very nicely with org using babel.
Ledger supports converting CSV files from banks into a ledger file
(section 7.2.1 of the ledger info manual).

-- 
: Eric S Fraga, with org release_9.5.2-293-gb8656a in Emacs 29.0.50
: Latest paper written in org: https://arxiv.org/abs/2106.05096

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Greg Minshall]

> I'm wondering if your opinion about Ledger isn't influenced by the fact
> that it uses basic accounting principles, like the idea of double-entry
> accounting.  For me, it's /very/ simple and intuitive, but I graduated
> in economics many years ago and had a (basic, but still) course on
> accounting.  If that is your problem, I'd suggest spending some time on
> understanding that - even if you don't end up using Ledger but some
> other software, you might find learning about accounting useful.  (Also,
> it's fascinating.  No, really!  It's an extremely clever abstract
> system with a real-life use-case.)

i agree that learning the principles of double-entry is worthwhile,
actually interesting, and of a very interesting history!

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 1306 bytes --]

The 'double reporting' idea might have been why I just flat out shook my head when I saw the one presentation on 'ledger' in Emacs on YouTube a while ago as its a bit too complicated and unnecessary for a layman.

I just don't need that kind of over-thinking when it comes to my finances since I just need a spreadsheet that listed expenses, and to count totals. That's about it.

I will take a look at the other suggestions with specific Org Mode formulas, looked awesome, thanks.

On Mon, Jan 10, 2022, at 10:31 AM, Greg Minshall wrote:
> > I'm wondering if your opinion about Ledger isn't influenced by the fact
> > that it uses basic accounting principles, like the idea of double-entry
> > accounting.  For me, it's /very/ simple and intuitive, but I graduated
> > in economics many years ago and had a (basic, but still) course on
> > accounting.  If that is your problem, I'd suggest spending some time on
> > understanding that - even if you don't end up using Ledger but some
> > other software, you might find learning about accounting useful.  (Also,
> > it's fascinating.  No, really!  It's an extremely clever abstract
> > system with a real-life use-case.)
> 
> i agree that learning the principles of double-entry is worthwhile,
> actually interesting, and of a very interesting history!
> 

[-- Attachment #2: Type: text/html, Size: 1817 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Bob Newell]

Aloha everyone,

I used ledger in emacs (with babel) for many years managing
the finances of two small non-profits. It did require learning
a few things but so does org-mode.

That's not to say that one size fits all, but ledger for me
took a lot of the work out of things, and once you learn how
to do it, double entry bookkeeping really makes financial
management easier.

I'm not saying that custom org-mode code is the "wrong" way to
go; the right way to go is whatever works for you, even if
that's (oh my) a single-entry Excel spreadsheet.

But my take is that once you learn ledger, and especially when
you integrate it into Emacs, you'll be sold.

-- 
Bob Newell
Honolulu, Hawai`i

- Via GNU/Linux/Emacs/Gnus/BBDB

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[John Hendy]

On Mon, Jan 10, 2022 at 11:09 AM Samuel Banya <sbanya@fastmail.com> wrote:
>
> The 'double reporting' idea might have been why I just flat out shook my head when I saw the one presentation on 'ledger' in Emacs on YouTube a while ago as its a bit too complicated and unnecessary for a layman.

Fair, it's a bit to wrap one's head around. That said, I think it's
also standard and one doesn't end up having to actually create two
entries for every transaction, for what it's worth.

> I just don't need that kind of over-thinking when it comes to my finances since I just need a spreadsheet that listed expenses, and to count totals. That's about it.

In that case, would a running csv where you paste in each month's new
pull work? You could even keep the totals to the side, so that as you
paste new data in e.g. cols A-D, your totals (in E and beyond) can sit
right there happily? I alluded to this with respect to the request to
automate csv -> org; without knowing which things must be org and why,
it's hard to comment on these kinds of inquiries. Maybe org is just
more fun, maybe it's mandatory because there's some other aspect of
this we don't know.

> I will take a look at the other suggestions with specific Org Mode formulas, looked awesome, thanks.

Happy to help and good luck!
John

> On Mon, Jan 10, 2022, at 10:31 AM, Greg Minshall wrote:
>
> > I'm wondering if your opinion about Ledger isn't influenced by the fact
> > that it uses basic accounting principles, like the idea of double-entry
> > accounting.  For me, it's /very/ simple and intuitive, but I graduated
> > in economics many years ago and had a (basic, but still) course on
> > accounting.  If that is your problem, I'd suggest spending some time on
> > understanding that - even if you don't end up using Ledger but some
> > other software, you might find learning about accounting useful.  (Also,
> > it's fascinating.  No, really!  It's an extremely clever abstract
> > system with a real-life use-case.)
>
> i agree that learning the principles of double-entry is worthwhile,
> actually interesting, and of a very interesting history!
>
>

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Marcin Borkowski]

On 2022-01-10, at 18:01, Samuel Banya <sbanya@fastmail.com> wrote:

> The 'double reporting' idea might have been why I just flat out shook my head when I saw the one presentation on 'ledger' in Emacs on YouTube a while ago as its a bit too complicated and unnecessary for a layman.
>
> I just don't need that kind of over-thinking when it comes to my finances since I just need a spreadsheet that listed expenses, and to count totals. That's about it.

You may think it's overthinking... unless you encounter one of the many
/real-world problems/ the double-accounting system was /designed/ to
solve.

A classic example is when you buy something on day X (expense) but pay
for it on day Y (cash outflow), and you want to be able to record both
dates.

Or you have some irregular costs (like paying for a car insurance once
a year) and you want to spread them more evenly over a longer period, so
that your /cash outflow/ is recorded once a year, but the associated
/cost/ is recorded every month.  (This may help tremendously with
budgeting.)

Or you decide to transfer money between two bank accounts (both yours),
but money going from account A arrives at account B after one day (or
even later).

Or you buy something for someone and he pays you back a few days (or
weeks, or months) later, or not at all (which may happen).


As you might notice, the first two examples are situations when the
/expense/ and /cash outflow/ are detached in time - something
/extremely/ common in business, but also pretty common in personal
finance.  You might say that you don't care - and I understand that -
but it is actually very useful to track /both/ expenses (so that you
know your cost of living etc.) and cash in- and outflows (so that you
can reconcile your ledger with your cash/bank account etc., to ensure
you didn't make any mistake).  (If you - like most people, I assume -
barely have liquidity, tracking cash flows may become even more
important.)  Also, the difference in time between income/expenses and
cash flow may become /extremely/ important when calculating taxes.

The third example is also connected with things happening at different
moments in time, though to be fair, if you're only interested in actual
/expenses/, you probably wouldn't even want to record that.

The last example is similar to the third one - it's not an /expense/ per
se, but again - tracking cash is useful, tracking your assets and
liabilities (in that case, money you owe to other people and money other
people owe to you) is /extremely/ useful, even just as an individual,
and if that guy /doesn't/ pay you back (mind you, this may happen for
/legitimate/ issues, like death), it can actually /become/ an expense.

Also, using accounting software makes things like calculating your net
worth trivial.  And there are other issues which can quickly become
pretty hairy when you try to wrap you head around them with a simple
spreadsheet (like dealing with various currencies/stock/commodities with
varying prices, for instance), but which have standard, well-known
solutions in the world of accounting.


Actually, I'd argue that learning the basics of accounting could help
a /layman/ better manage their finances.  While the technicalities of
bookkeeping are not relevant to that, the general ideas like cash flow,
income and expenses, balance sheet, assets and liabilities, liquidity,
compound interest etc. provide a very useful way of thinking about
money.  Two examples.  A friend of mine (an economist) was asked to
conduct a 45-minute class for high-schoolers about economics (and
finance, I guess).  He decided that if these kids are to have any use of
that class, he should gice them a few sentences - "anchors", I'd call
them - which could be some kind of guidelines to use when navigating the
world of economy (on a scale of a country as well as of a household) and
finance.  One of these sentences was: "If I don't have enough cash to
buy <blank>, it means I cannot afford it."  It's as simple as that - but
it goes very much against the message you get from (among others) the
banking system.  (And yes, there are "exceptions".  But you can learn
about those later.)  The second example is actually my pet peeve - some
people buy things and call them "investments", probably to (possibly
unconsciously) justify these expenses.  Usually these expenses have
/nothing/ to do with investments.  Buying a car is /not/ an investment
unless you use it to earn money.  Buying a better tv is /not/ an
investment.  Buying a house /can/ be an investment if it allows you to
save on rent.  Simply put: an "investment" is an expense you make that
will enable you to generate income (or at least cut costs) in the future
(with reasonable certainty or at least reasonable probability - buying
a lottery ticket is /not/ an investment).


Anyway, if any of the above sounds like it might be useful for you, I'd
strongly suggest learning the basics of double accounting.  It's neither
very hard nor boring!

Best,
mb



>
> I will take a look at the other suggestions with specific Org Mode formulas, looked awesome, thanks.
>
> On Mon, Jan 10, 2022, at 10:31 AM, Greg Minshall wrote:
>> > I'm wondering if your opinion about Ledger isn't influenced by the fact
>> > that it uses basic accounting principles, like the idea of double-entry
>> > accounting.  For me, it's /very/ simple and intuitive, but I graduated
>> > in economics many years ago and had a (basic, but still) course on
>> > accounting.  If that is your problem, I'd suggest spending some time on
>> > understanding that - even if you don't end up using Ledger but some
>> > other software, you might find learning about accounting useful.  (Also,
>> > it's fascinating.  No, really!  It's an extremely clever abstract
>> > system with a real-life use-case.)
>> 
>> i agree that learning the principles of double-entry is worthwhile,
>> actually interesting, and of a very interesting history!
>> 


-- 
Marcin Borkowski
http://mbork.pl

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Wales]

fwiw, my experience: double entry is what i found ledger useful for.
iiuc it is that transactions go from one place to another, which felt
intuitive to me.

anything else, such as taking monthly averages, didn't end up as i
expected.  weird concept of average to me, and v3 bugs with floating
point etc.  i could never get them to be pretty.

so the example you give of ledger's rough edge is actually the one
non-rough-edge i found in ledger.  in org, i hae a shell babel block
ad an exampale or ledge block for data.

i find it strange that agenda searching doesn't find stuff in the
ledger data block, and would want org dates if poss.  i did not try to
use ledger properly with its emacs mode.


On 1/10/22, Marcin Borkowski <mbork@mbork.pl> wrote:
>
> On 2022-01-10, at 18:01, Samuel Banya <sbanya@fastmail.com> wrote:
>
>> The 'double reporting' idea might have been why I just flat out shook my
>> head when I saw the one presentation on 'ledger' in Emacs on YouTube a
>> while ago as its a bit too complicated and unnecessary for a layman.
>>
>> I just don't need that kind of over-thinking when it comes to my finances
>> since I just need a spreadsheet that listed expenses, and to count totals.
>> That's about it.
>
> You may think it's overthinking... unless you encounter one of the many
> /real-world problems/ the double-accounting system was /designed/ to
> solve.
>
> A classic example is when you buy something on day X (expense) but pay
> for it on day Y (cash outflow), and you want to be able to record both
> dates.
>
> Or you have some irregular costs (like paying for a car insurance once
> a year) and you want to spread them more evenly over a longer period, so
> that your /cash outflow/ is recorded once a year, but the associated
> /cost/ is recorded every month.  (This may help tremendously with
> budgeting.)
>
> Or you decide to transfer money between two bank accounts (both yours),
> but money going from account A arrives at account B after one day (or
> even later).
>
> Or you buy something for someone and he pays you back a few days (or
> weeks, or months) later, or not at all (which may happen).
>
>
> As you might notice, the first two examples are situations when the
> /expense/ and /cash outflow/ are detached in time - something
> /extremely/ common in business, but also pretty common in personal
> finance.  You might say that you don't care - and I understand that -
> but it is actually very useful to track /both/ expenses (so that you
> know your cost of living etc.) and cash in- and outflows (so that you
> can reconcile your ledger with your cash/bank account etc., to ensure
> you didn't make any mistake).  (If you - like most people, I assume -
> barely have liquidity, tracking cash flows may become even more
> important.)  Also, the difference in time between income/expenses and
> cash flow may become /extremely/ important when calculating taxes.
>
> The third example is also connected with things happening at different
> moments in time, though to be fair, if you're only interested in actual
> /expenses/, you probably wouldn't even want to record that.
>
> The last example is similar to the third one - it's not an /expense/ per
> se, but again - tracking cash is useful, tracking your assets and
> liabilities (in that case, money you owe to other people and money other
> people owe to you) is /extremely/ useful, even just as an individual,
> and if that guy /doesn't/ pay you back (mind you, this may happen for
> /legitimate/ issues, like death), it can actually /become/ an expense.
>
> Also, using accounting software makes things like calculating your net
> worth trivial.  And there are other issues which can quickly become
> pretty hairy when you try to wrap you head around them with a simple
> spreadsheet (like dealing with various currencies/stock/commodities with
> varying prices, for instance), but which have standard, well-known
> solutions in the world of accounting.
>
>
> Actually, I'd argue that learning the basics of accounting could help
> a /layman/ better manage their finances.  While the technicalities of
> bookkeeping are not relevant to that, the general ideas like cash flow,
> income and expenses, balance sheet, assets and liabilities, liquidity,
> compound interest etc. provide a very useful way of thinking about
> money.  Two examples.  A friend of mine (an economist) was asked to
> conduct a 45-minute class for high-schoolers about economics (and
> finance, I guess).  He decided that if these kids are to have any use of
> that class, he should gice them a few sentences - "anchors", I'd call
> them - which could be some kind of guidelines to use when navigating the
> world of economy (on a scale of a country as well as of a household) and
> finance.  One of these sentences was: "If I don't have enough cash to
> buy <blank>, it means I cannot afford it."  It's as simple as that - but
> it goes very much against the message you get from (among others) the
> banking system.  (And yes, there are "exceptions".  But you can learn
> about those later.)  The second example is actually my pet peeve - some
> people buy things and call them "investments", probably to (possibly
> unconsciously) justify these expenses.  Usually these expenses have
> /nothing/ to do with investments.  Buying a car is /not/ an investment
> unless you use it to earn money.  Buying a better tv is /not/ an
> investment.  Buying a house /can/ be an investment if it allows you to
> save on rent.  Simply put: an "investment" is an expense you make that
> will enable you to generate income (or at least cut costs) in the future
> (with reasonable certainty or at least reasonable probability - buying
> a lottery ticket is /not/ an investment).
>
>
> Anyway, if any of the above sounds like it might be useful for you, I'd
> strongly suggest learning the basics of double accounting.  It's neither
> very hard nor boring!
>
> Best,
> mb
>
>
>
>>
>> I will take a look at the other suggestions with specific Org Mode
>> formulas, looked awesome, thanks.
>>
>> On Mon, Jan 10, 2022, at 10:31 AM, Greg Minshall wrote:
>>> > I'm wondering if your opinion about Ledger isn't influenced by the
>>> > fact
>>> > that it uses basic accounting principles, like the idea of
>>> > double-entry
>>> > accounting.  For me, it's /very/ simple and intuitive, but I graduated
>>> > in economics many years ago and had a (basic, but still) course on
>>> > accounting.  If that is your problem, I'd suggest spending some time
>>> > on
>>> > understanding that - even if you don't end up using Ledger but some
>>> > other software, you might find learning about accounting useful.
>>> > (Also,
>>> > it's fascinating.  No, really!  It's an extremely clever abstract
>>> > system with a real-life use-case.)
>>>
>>> i agree that learning the principles of double-entry is worthwhile,
>>> actually interesting, and of a very interesting history!
>>>
>
>
> --
> Marcin Borkowski
> http://mbork.pl
>
>


-- 
The Kafka Pandemic

A blog about science, health, human rights, and misopathy:
https://thekafkapandemic.blogspot.com

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 7370 bytes --]

The double reporting thing still doesn't apply to me.

Even if you pay with credit cards, ultimately it comes out of your regular bank account to pay off.

To me, its easier to just focus on the single bank account, and obtain the latest .csv from the current week (Last Saturday to current Saturday), and treat the entire list as a list of negative value expenses, and positive income items to keep it super simple.

The simpler it is, the better for me, because I honestly hate doing finances, and only do them to make sure I'm ontop of everything as its the last thing I want to do on a given week.

Hope that helps give some background, since I don't want to appear to be difficult, but would rather just "keep it in a spreadsheet".

To kind of back it up just a slight bit, even the average Excel user would say the same exact thing, especially ones that try to avoid having to use Quicken or more complicated pieces of software (which I know, there DOES exist GNU Cash, etc).

But yeah, keep it simple, keep it Emacs, keep it Org Mode, and keep it fun :) No need for Ledger.

On Mon, Jan 10, 2022, at 2:04 PM, Marcin Borkowski wrote:
> 
> On 2022-01-10, at 18:01, Samuel Banya <sbanya@fastmail.com> wrote:
> 
> > The 'double reporting' idea might have been why I just flat out shook my head when I saw the one presentation on 'ledger' in Emacs on YouTube a while ago as its a bit too complicated and unnecessary for a layman.
> >
> > I just don't need that kind of over-thinking when it comes to my finances since I just need a spreadsheet that listed expenses, and to count totals. That's about it.
> 
> You may think it's overthinking... unless you encounter one of the many
> /real-world problems/ the double-accounting system was /designed/ to
> solve.
> 
> A classic example is when you buy something on day X (expense) but pay
> for it on day Y (cash outflow), and you want to be able to record both
> dates.
> 
> Or you have some irregular costs (like paying for a car insurance once
> a year) and you want to spread them more evenly over a longer period, so
> that your /cash outflow/ is recorded once a year, but the associated
> /cost/ is recorded every month.  (This may help tremendously with
> budgeting.)
> 
> Or you decide to transfer money between two bank accounts (both yours),
> but money going from account A arrives at account B after one day (or
> even later).
> 
> Or you buy something for someone and he pays you back a few days (or
> weeks, or months) later, or not at all (which may happen).
> 
> 
> As you might notice, the first two examples are situations when the
> /expense/ and /cash outflow/ are detached in time - something
> /extremely/ common in business, but also pretty common in personal
> finance.  You might say that you don't care - and I understand that -
> but it is actually very useful to track /both/ expenses (so that you
> know your cost of living etc.) and cash in- and outflows (so that you
> can reconcile your ledger with your cash/bank account etc., to ensure
> you didn't make any mistake).  (If you - like most people, I assume -
> barely have liquidity, tracking cash flows may become even more
> important.)  Also, the difference in time between income/expenses and
> cash flow may become /extremely/ important when calculating taxes.
> 
> The third example is also connected with things happening at different
> moments in time, though to be fair, if you're only interested in actual
> /expenses/, you probably wouldn't even want to record that.
> 
> The last example is similar to the third one - it's not an /expense/ per
> se, but again - tracking cash is useful, tracking your assets and
> liabilities (in that case, money you owe to other people and money other
> people owe to you) is /extremely/ useful, even just as an individual,
> and if that guy /doesn't/ pay you back (mind you, this may happen for
> /legitimate/ issues, like death), it can actually /become/ an expense.
> 
> Also, using accounting software makes things like calculating your net
> worth trivial.  And there are other issues which can quickly become
> pretty hairy when you try to wrap you head around them with a simple
> spreadsheet (like dealing with various currencies/stock/commodities with
> varying prices, for instance), but which have standard, well-known
> solutions in the world of accounting.
> 
> 
> Actually, I'd argue that learning the basics of accounting could help
> a /layman/ better manage their finances.  While the technicalities of
> bookkeeping are not relevant to that, the general ideas like cash flow,
> income and expenses, balance sheet, assets and liabilities, liquidity,
> compound interest etc. provide a very useful way of thinking about
> money.  Two examples.  A friend of mine (an economist) was asked to
> conduct a 45-minute class for high-schoolers about economics (and
> finance, I guess).  He decided that if these kids are to have any use of
> that class, he should gice them a few sentences - "anchors", I'd call
> them - which could be some kind of guidelines to use when navigating the
> world of economy (on a scale of a country as well as of a household) and
> finance.  One of these sentences was: "If I don't have enough cash to
> buy <blank>, it means I cannot afford it."  It's as simple as that - but
> it goes very much against the message you get from (among others) the
> banking system.  (And yes, there are "exceptions".  But you can learn
> about those later.)  The second example is actually my pet peeve - some
> people buy things and call them "investments", probably to (possibly
> unconsciously) justify these expenses.  Usually these expenses have
> /nothing/ to do with investments.  Buying a car is /not/ an investment
> unless you use it to earn money.  Buying a better tv is /not/ an
> investment.  Buying a house /can/ be an investment if it allows you to
> save on rent.  Simply put: an "investment" is an expense you make that
> will enable you to generate income (or at least cut costs) in the future
> (with reasonable certainty or at least reasonable probability - buying
> a lottery ticket is /not/ an investment).
> 
> 
> Anyway, if any of the above sounds like it might be useful for you, I'd
> strongly suggest learning the basics of double accounting.  It's neither
> very hard nor boring!
> 
> Best,
> mb
> 
> 
> 
> >
> > I will take a look at the other suggestions with specific Org Mode formulas, looked awesome, thanks.
> >
> > On Mon, Jan 10, 2022, at 10:31 AM, Greg Minshall wrote:
> >> > I'm wondering if your opinion about Ledger isn't influenced by the fact
> >> > that it uses basic accounting principles, like the idea of double-entry
> >> > accounting.  For me, it's /very/ simple and intuitive, but I graduated
> >> > in economics many years ago and had a (basic, but still) course on
> >> > accounting.  If that is your problem, I'd suggest spending some time on
> >> > understanding that - even if you don't end up using Ledger but some
> >> > other software, you might find learning about accounting useful.  (Also,
> >> > it's fascinating.  No, really!  It's an extremely clever abstract
> >> > system with a real-life use-case.)
> >> 
> >> i agree that learning the principles of double-entry is worthwhile,
> >> actually interesting, and of a very interesting history!
> >> 
> 
> 
> -- 
> Marcin Borkowski
> http://mbork.pl
> 

[-- Attachment #2: Type: text/html, Size: 9562 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[John Hendy]

On Mon, Jan 10, 2022 at 6:32 PM Samuel Banya <sbanya@fastmail.com> wrote:
>
> The double reporting thing still doesn't apply to me.
>
> Even if you pay with credit cards, ultimately it comes out of your regular bank account to pay off.
>
> To me, its easier to just focus on the single bank account, and obtain the latest .csv from the current week (Last Saturday to current Saturday), and treat the entire list as a list of negative value expenses, and positive income items to keep it super simple.
>
> The simpler it is, the better for me, because I honestly hate doing finances, and only do them to make sure I'm ontop of everything as its the last thing I want to do on a given week.
>
> Hope that helps give some background, since I don't want to appear to be difficult, but would rather just "keep it in a spreadsheet".
>
> To kind of back it up just a slight bit, even the average Excel user would say the same exact thing, especially ones that try to avoid having to use Quicken or more complicated pieces of software (which I know, there DOES exist GNU Cash, etc).
>
> But yeah, keep it simple, keep it Emacs, keep it Org Mode, and keep it fun :) No need for Ledger.

I totally get the resistance to ledger from having tried it myself for
a bit, but that last line made me chuckle out loud. No one using emacs
and org-mode is keeping it *that* simple :p

The simplest answer is probably Mint.

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 1579 bytes --]

Very true, my config would scare the average Emacs user as its too complex :)

On Mon, Jan 10, 2022, at 7:41 PM, John Hendy wrote:
> On Mon, Jan 10, 2022 at 6:32 PM Samuel Banya <sbanya@fastmail.com> wrote:
> >
> > The double reporting thing still doesn't apply to me.
> >
> > Even if you pay with credit cards, ultimately it comes out of your regular bank account to pay off.
> >
> > To me, its easier to just focus on the single bank account, and obtain the latest .csv from the current week (Last Saturday to current Saturday), and treat the entire list as a list of negative value expenses, and positive income items to keep it super simple.
> >
> > The simpler it is, the better for me, because I honestly hate doing finances, and only do them to make sure I'm ontop of everything as its the last thing I want to do on a given week.
> >
> > Hope that helps give some background, since I don't want to appear to be difficult, but would rather just "keep it in a spreadsheet".
> >
> > To kind of back it up just a slight bit, even the average Excel user would say the same exact thing, especially ones that try to avoid having to use Quicken or more complicated pieces of software (which I know, there DOES exist GNU Cash, etc).
> >
> > But yeah, keep it simple, keep it Emacs, keep it Org Mode, and keep it fun :) No need for Ledger.
> 
> I totally get the resistance to ledger from having tried it myself for
> a bit, but that last line made me chuckle out loud. No one using emacs
> and org-mode is keeping it *that* simple :p
> 
> The simplest answer is probably Mint.
> 

[-- Attachment #2: Type: text/html, Size: 2179 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Quiliro Ordóñez]

El 2022-01-09 22:42, Samuel Banya escribió:
> Ah, Ledger is way too complicated for what it's worth. I've seen
> videos on YouTube about it, and every person who's praised it is
> pretty much too smart enough to be able to explain it in simple terms.

It is very easy.  I made a video for EmacsConf 2019
https://emacsconf.org/2019/talks/12/ .   I wish someone had showed me
examples like these when I started with it.  

My fiancee is a preschool teacher (programming is not her area of
expertize).  But she quickly learned how to use it.  She keeps about
3700 per year with 4500 lines.  It takes her about 10 minutes per day. 
With that investment, she can even tell how much we have spent on apples
or on all fruits (as detailed or general as she wants).  On these two
years, she has saved us about half our budget and has us living much
better thanks to the knowledge of what we want to invest on.  (We now do
not think of how to spend money, but how to invest it.  That changes the
point view completely.)
  
There are complicated reports as well as very simple ones that can be
generated.  And you can always customize them to the last bit.  There is
also a website http://plaintextaccounting.org .  There is plenty of
information there.  You can also use org-babel to include formulas for
Ledger reports.

Just ask away!  :-)

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 1898 bytes --]

Hello,

I appreciate the enthusiasm for Ledger, but I really just don't like the double posting idea period.

I just need totals spreadsheet style, that's about it. I think the whole idea behind bean count is tedious.

I just don't think like a mathematician in this respect, too brainy for me.

I'll take a look at the Org Mode way to solve this problem via the previous posts, thanks.

Sincerely,

Sam

On Fri, Jan 14, 2022, at 2:54 PM, Quiliro Ordóñez wrote:
> El 2022-01-09 22:42, Samuel Banya escribió:
> > Ah, Ledger is way too complicated for what it's worth. I've seen
> > videos on YouTube about it, and every person who's praised it is
> > pretty much too smart enough to be able to explain it in simple terms.
> 
> It is very easy.  I made a video for EmacsConf 2019
> https://emacsconf.org/2019/talks/12/ .   I wish someone had showed me
> examples like these when I started with it.  
> 
> My fiancee is a preschool teacher (programming is not her area of
> expertize).  But she quickly learned how to use it.  She keeps about
> 3700 per year with 4500 lines.  It takes her about 10 minutes per day. 
> With that investment, she can even tell how much we have spent on apples
> or on all fruits (as detailed or general as she wants).  On these two
> years, she has saved us about half our budget and has us living much
> better thanks to the knowledge of what we want to invest on.  (We now do
> not think of how to spend money, but how to invest it.  That changes the
> point view completely.)
>   
> There are complicated reports as well as very simple ones that can be
> generated.  And you can always customize them to the last bit.  There is
> also a website http://plaintextaccounting.org .  There is plenty of
> information there.  You can also use org-babel to include formulas for
> Ledger reports.
> 
> Just ask away!  :-)
> 
> 

[-- Attachment #2: Type: text/html, Size: 2800 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 2661 bytes --]


> In that case, would a running csv where you paste in each month's new
> pull work? You could even keep the totals to the side, so that as you
> paste new data in e.g. cols A-D, your totals (in E and beyond) can sit
> right there happily? I alluded to this with respect to the request to
> automate csv -> org; without knowing which things must be org and why,
> it's hard to comment on these kinds of inquiries. Maybe org is just
> more fun, maybe it's mandatory because there's some other aspect of
> this we don't know.

How would you able to keep the running totals in Column E?

Can you provide me with an example of this?

On Sun, Jan 16, 2022, at 10:15 AM, Samuel Banya wrote:
> Hello,
> 
> I appreciate the enthusiasm for Ledger, but I really just don't like the double posting idea period.
> 
> I just need totals spreadsheet style, that's about it. I think the whole idea behind bean count is tedious.
> 
> I just don't think like a mathematician in this respect, too brainy for me.
> 
> I'll take a look at the Org Mode way to solve this problem via the previous posts, thanks.
> 
> Sincerely,
> 
> Sam
> 
> On Fri, Jan 14, 2022, at 2:54 PM, Quiliro Ordóñez wrote:
>> El 2022-01-09 22:42, Samuel Banya escribió:
>> > Ah, Ledger is way too complicated for what it's worth. I've seen
>> > videos on YouTube about it, and every person who's praised it is
>> > pretty much too smart enough to be able to explain it in simple terms.
>> 
>> It is very easy.  I made a video for EmacsConf 2019
>> https://emacsconf.org/2019/talks/12/ .   I wish someone had showed me
>> examples like these when I started with it.  
>> 
>> My fiancee is a preschool teacher (programming is not her area of
>> expertize).  But she quickly learned how to use it.  She keeps about
>> 3700 per year with 4500 lines.  It takes her about 10 minutes per day. 
>> With that investment, she can even tell how much we have spent on apples
>> or on all fruits (as detailed or general as she wants).  On these two
>> years, she has saved us about half our budget and has us living much
>> better thanks to the knowledge of what we want to invest on.  (We now do
>> not think of how to spend money, but how to invest it.  That changes the
>> point view completely.)
>>   
>> There are complicated reports as well as very simple ones that can be
>> generated.  And you can always customize them to the last bit.  There is
>> also a website http://plaintextaccounting.org .  There is plenty of
>> information there.  You can also use org-babel to include formulas for
>> Ledger reports.
>> 
>> Just ask away!  :-)
>> 
>> 
> 

[-- Attachment #2: Type: text/html, Size: 3816 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Samuel Banya]

[-- Attachment #1: Type: text/plain, Size: 4199 bytes --]

Actually, to end this thread, I actually used Eric Fraga's idea to just use separate sections for each month, since this solves this problem indefinitely, so thanks for the ideas in this thread as my workflow is definitely improved for my finance spreadsheet.

Cool stuff, will include his quote to wrap it up:

> I would suggest the following:
> 
> |------------+-----------------------------------------------------+---+----------|
> | 01/03/2022 | Example Rent Expense                                |   | -1061.67 |
> | 01/04/2022 | Example Food Expense                                |   | -1061.67 |
> |------------+-----------------------------------------------------+---+----------|
> |            | January expenses                                    | 0 | -2123.34 |
> |------------+-----------------------------------------------------+---+----------|
> | 02/05/2022 | Example Utility Expense                             |   | -2061.67 |
> | 02/06/2022 | Example Random Expense                              |   | -2061.67 |
> |------------+-----------------------------------------------------+---+----------|
> |            | February expenses                                   | 0 | -4123.34 |
> |------------+-----------------------------------------------------+---+----------|
> #+TBLFM: @3$3=vsum(@-I..@-II)::@3$4=vsum(@-I..@-II)::@6$3=vsum(@-I..@-II)::@6$4=vsum(@-I..@-II)

On Sun, Jan 16, 2022, at 10:19 AM, Samuel Banya wrote:
> 
>> In that case, would a running csv where you paste in each month's new
>> pull work? You could even keep the totals to the side, so that as you
>> paste new data in e.g. cols A-D, your totals (in E and beyond) can sit
>> right there happily? I alluded to this with respect to the request to
>> automate csv -> org; without knowing which things must be org and why,
>> it's hard to comment on these kinds of inquiries. Maybe org is just
>> more fun, maybe it's mandatory because there's some other aspect of
>> this we don't know.
> 
> How would you able to keep the running totals in Column E?
> 
> Can you provide me with an example of this?
> 
> On Sun, Jan 16, 2022, at 10:15 AM, Samuel Banya wrote:
>> Hello,
>> 
>> I appreciate the enthusiasm for Ledger, but I really just don't like the double posting idea period.
>> 
>> I just need totals spreadsheet style, that's about it. I think the whole idea behind bean count is tedious.
>> 
>> I just don't think like a mathematician in this respect, too brainy for me.
>> 
>> I'll take a look at the Org Mode way to solve this problem via the previous posts, thanks.
>> 
>> Sincerely,
>> 
>> Sam
>> 
>> On Fri, Jan 14, 2022, at 2:54 PM, Quiliro Ordóñez wrote:
>>> El 2022-01-09 22:42, Samuel Banya escribió:
>>> > Ah, Ledger is way too complicated for what it's worth. I've seen
>>> > videos on YouTube about it, and every person who's praised it is
>>> > pretty much too smart enough to be able to explain it in simple terms.
>>> 
>>> It is very easy.  I made a video for EmacsConf 2019
>>> https://emacsconf.org/2019/talks/12/ .   I wish someone had showed me
>>> examples like these when I started with it.  
>>> 
>>> My fiancee is a preschool teacher (programming is not her area of
>>> expertize).  But she quickly learned how to use it.  She keeps about
>>> 3700 per year with 4500 lines.  It takes her about 10 minutes per day. 
>>> With that investment, she can even tell how much we have spent on apples
>>> or on all fruits (as detailed or general as she wants).  On these two
>>> years, she has saved us about half our budget and has us living much
>>> better thanks to the knowledge of what we want to invest on.  (We now do
>>> not think of how to spend money, but how to invest it.  That changes the
>>> point view completely.)
>>>   
>>> There are complicated reports as well as very simple ones that can be
>>> generated.  And you can always customize them to the last bit.  There is
>>> also a website http://plaintextaccounting.org .  There is plenty of
>>> information there.  You can also use org-babel to include formulas for
>>> Ledger reports.
>>> 
>>> Just ask away!  :-)
>>> 
>>> 
>> 
> 

[-- Attachment #2: Type: text/html, Size: 6718 bytes --]

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Eric S Fraga]

On Sunday, 16 Jan 2022 at 10:35, Samuel Banya wrote:
>     How would you able to keep the running totals in Column E?

Using your initial example, adding a header line to give a starting
point, the following works:

| Date       | Item                    |   |    Debit |          |
|------------+-------------------------+---+----------+----------|
| 01/03/2022 | Example Rent Expense    |   | -1061.67 | -1061.67 |
| 01/04/2022 | Example Food Expense    |   | -1061.67 | -2123.34 |
|------------+-------------------------+---+----------+----------|
| 02/05/2022 | Example Utility Expense |   | -2061.67 | -4185.01 |
| 02/06/2022 | Example Random Expense  |   | -2061.67 | -6246.68 |
|------------+-------------------------+---+----------+----------|
#+TBLFM: $5=@-1+$-1;NE

I've not included monthly sums as this would complicate things (although
should still be possible...) but retain a dividing line for clarity.

The top entry of the last column could have a starting balance, if you
wanted.  At the moment, it's treated as a 0.00 value.

-- 
: Eric S Fraga, with org release_9.5.2-306-g9623da in Emacs 29.0.50

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[John Hendy]

[-- Attachment #1.1: Type: text/plain, Size: 3543 bytes --]

On Sun, Jan 16, 2022 at 9:20 AM Samuel Banya <sbanya@fastmail.com> wrote:

>
> In that case, would a running csv where you paste in each month's new
> pull work? You could even keep the totals to the side, so that as you
> paste new data in e.g. cols A-D, your totals (in E and beyond) can sit
> right there happily? I alluded to this with respect to the request to
> automate csv -> org; without knowing which things must be org and why,
> it's hard to comment on these kinds of inquiries. Maybe org is just
> more fun, maybe it's mandatory because there's some other aspect of
> this we don't know.
>
>
> How would you able to keep the running totals in Column E?
>
> Can you provide me with an example of this?
>

Sure, attached.
- running csv, named "running_total.csv"
- toy examples of your jan and feb downloads

Routine each month:
- open month's bank download
- copy all but the header (easy to do with ctrl+shift+arrow to get to
bottom row)
- ctrl+arrow to get to last row in running_totals.csv
- update the month's formula with
sum(first_row_of_this_month:last_row_of_this_month)
- you could  even get fancy with an auto-date formula I'm sure

Just keep the total formulas far enough right (I said column E, but it's
arbitrary) to not get pasted over depending on your institution's number of
columns.

This is basically doing what you're doing in org, but in a csv (or use a
regular ods or xlsx if you need the last month's formulas to stay as
formulas, not static values on save/exit).

John



> On Sun, Jan 16, 2022, at 10:15 AM, Samuel Banya wrote:
>
> Hello,
>
> I appreciate the enthusiasm for Ledger, but I really just don't like the
> double posting idea period.
>
> I just need totals spreadsheet style, that's about it. I think the whole
> idea behind bean count is tedious.
>
> I just don't think like a mathematician in this respect, too brainy for me.
>
> I'll take a look at the Org Mode way to solve this problem via the
> previous posts, thanks.
>
> Sincerely,
>
> Sam
>
> On Fri, Jan 14, 2022, at 2:54 PM, Quiliro Ordóñez wrote:
>
> El 2022-01-09 22:42, Samuel Banya escribió:
> > Ah, Ledger is way too complicated for what it's worth. I've seen
> > videos on YouTube about it, and every person who's praised it is
> > pretty much too smart enough to be able to explain it in simple terms.
>
> It is very easy.  I made a video for EmacsConf 2019
> https://emacsconf.org/2019/talks/12/ .   I wish someone had showed me
> examples like these when I started with it.
>
> My fiancee is a preschool teacher (programming is not her area of
> expertize).  But she quickly learned how to use it.  She keeps about
> 3700 per year with 4500 lines.  It takes her about 10 minutes per day.
> With that investment, she can even tell how much we have spent on apples
> or on all fruits (as detailed or general as she wants).  On these two
> years, she has saved us about half our budget and has us living much
> better thanks to the knowledge of what we want to invest on.  (We now do
> not think of how to spend money, but how to invest it.  That changes the
> point view completely.)
>
> There are complicated reports as well as very simple ones that can be
> generated.  And you can always customize them to the last bit.  There is
> also a website http://plaintextaccounting.org .  There is plenty of
> information there.  You can also use org-babel to include formulas for
> Ledger reports.
>
> Just ask away!  :-)
>
>
>
>
>

[-- Attachment #1.2: Type: text/html, Size: 5339 bytes --]

[-- Attachment #2: running_total.csv --]
[-- Type: text/csv, Size: 217 bytes --]

"date","value",,,,
"jan",1,,,"jan",6
"jan",2,,,"feb",30
"jan",3,,,"mar",
"feb",4,,,"arp",
"feb",5,,,"may",
"feb",6,,,"jun",
"feb",7,,,"jul",
"feb",8,,,"aug",
,,,,"sep",
,,,,"oct",
,,,,"nov",
,,,,"dec",
,,,,"total",36

[-- Attachment #3: jan.csv --]
[-- Type: text/csv, Size: 39 bytes --]

"date","value"
"jan",1
"jan",2
"jan",3

[-- Attachment #4: feb.csv --]
[-- Type: text/csv, Size: 55 bytes --]

"date","value"
"feb",4
"feb",5
"feb",6
"feb",7
"feb",8

Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet

[Neil Jerram]

On Mon, 10 Jan 2022 at 10:31, Neil Jerram <neiljerram@gmail.com> wrote:
>
> On Mon, 10 Jan 2022 at 03:42, Samuel Banya <sbanya@fastmail.com> wrote:
> >
> > Ah, Ledger is way too complicated for what it's worth. I've seen videos on YouTube about it, and every person who's praised it is pretty much too smart enough to be able to explain it in simple terms.
>
> I have similar observations.  I don't (so far) see that Ledger (or
> alternatives) offers enough added value to be worth incorporating in
> my workflow, compared to directly processing the raw data.
>
> The value that would be of interest to me is:
> - Classifying/grouping transactions at analysis time (not entry time)
> according to a set of rules/regexps etc.
> - Drawing insights by analysing past expenditure, with that kind of
> classification.
> - Using such insights as part of future budgeting.
> - An overall functional approach, where the raw data is never changed,
> and the other inputs to an analysis can be tweaked on each run.
>
> I wish plaintextaccounting.org had a forum/ML for discussing this kind
> of thing, as it isn't specific to any of ledger/hledger/beancount in
> particular.

I have been reading the Ledger manual, and wanted to follow up because
it turns out Ledger (with ledger-autosync) actually *can* do the
workflows that I'm interested in (above).

- ledger-autosync can read my OFX files and output Ledger transactions
with Expenses:Unknown everywhere.

- Ledger supports account definitions with regexps, that mean "if you
see a transaction with *Unknown, and the description matches this
regexp, treat the *Unknown as actually this account".

- Then I can execute whatever reporting I need.

So, although it's not Ledger's primary use case, it seems Ledger can
operate "functionally" with the OFX files as the raw data and account
definitions saying how to categorize those, and considering the
generated Ledger journal to be ephemeral.

Assuming I don't run into gotchas, I reckon that makes me a Ledger
convert, and I can discard all my own code.

Best wishes,
     Neil