On Sun, Jan 9, 2022 at 11:17 AM Samuel Banya <sba...@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 >
