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
