emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet
@ 2022-01-09 16:56 Samuel Banya
  2022-01-09 19:05 ` Ken Mankoff
                   ` (3 more replies)
  0 siblings, 4 replies; 25+ messages in thread
From: Samuel Banya @ 2022-01-09 16:56 UTC (permalink / raw)
  To: Charles Berry

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

^ permalink raw reply	[flat|nested] 25+ messages in thread

end of thread, other threads:[~2022-01-19 15:23 UTC | newest]

Thread overview: 25+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
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
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

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).