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

[Ken Mankoff <mankoff@gmail.com>]

[-- 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 --]