From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp12.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms0.migadu.com with LMTPS id 2DFUAVU822G2fAEAgWs5BA (envelope-from ) for ; Sun, 09 Jan 2022 20:49:41 +0100 Received: from aspmx1.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp12.migadu.com with LMTPS id IOKvOVQ822FRUgEAauVa8A (envelope-from ) for ; Sun, 09 Jan 2022 20:49:40 +0100 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 4E3892E29E for ; Sun, 9 Jan 2022 20:49:40 +0100 (CET) Received: from localhost ([::1]:51494 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1n6eCB-0004ee-If for larch@yhetil.org; Sun, 09 Jan 2022 14:49:39 -0500 Received: from eggs.gnu.org ([209.51.188.92]:60888) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1n6eBO-0004eW-TT for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 14:48:50 -0500 Received: from [2607:f8b0:4864:20::92b] (port=42560 helo=mail-ua1-x92b.google.com) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1n6eBM-0002x0-N0 for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 14:48:50 -0500 Received: by mail-ua1-x92b.google.com with SMTP id p1so19983359uap.9 for ; Sun, 09 Jan 2022 11:48:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc:content-transfer-encoding; bh=wD0qt+G6EiTNJL6mpIHqn6UlORMMS4f91i3dLaFq3AU=; b=HTQrQ08K2qG8kPEOl0bfF6JVTRKNAhecNDh9LQHZybXynoA3n50VYsI8xnHEUf/xjU vlB7yIviIAmTRMV95/o9zQMXalt/COdNLsYXWK9FoQiGuENYiXuVAWCvR35tOcPdNWIn iDcHPg1eT5w6x/GPT7Irdr409we9h9Lypor6CWTjfQ0eQYRwG5bFxaF37sa5CLge85Rs 8aqWjvvI/3gnAFM8sR1fEu6TFkztvcRDPOUf8gEjjtdOuU1i5LpBvtT0B/08rKUUUKGq T8B1gOcBasATr/rkOdNfw8O5K4eLdAKondmAeXJMmmNPZ2Al7ivp000RuhnQvt1NOhXe hPUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc:content-transfer-encoding; bh=wD0qt+G6EiTNJL6mpIHqn6UlORMMS4f91i3dLaFq3AU=; b=DDt62RrM80GEF4GFX7cjmkmznUU2NE5AvGTVrBkWgxHyXdG/aDrzDWIhlqfnP3v7i/ 79G522JL9wQLmV6yGDRAI8PKCT6OavRBZuYYhTXRZPetamA1mbBH4MERz+pJhR1GZrwz HxYITk2p/y4ejVyAMrSSgILRDNmIhhgFRkQBXxBMFl2/KU9uPgnuIA9txgIHrk3qazut x2dYgl5Nk2OWbOgHBYVc+7IdflZ7+lpwGWlFzi7+09R3AmAup8BMXCCEkIq6ZdVfJrGd iaLxXD4aH+TfNI+/ZJTAfNyXAMOJ4S1iJvQeOP7zKqSCO2w0ADYWmgKk4kReo5a8hf/C bRlg== X-Gm-Message-State: AOAM530eKFGg7D1b7Hr95/rMvhCeMyVgHbC0TTQNGvpBtkTPKv7w9aXv 9zDap6Z84O8/TOinjoAk0fipoH5Bhxnc5smEmbA= X-Google-Smtp-Source: ABdhPJxsGnlwUyWMnrVS22nrdmQQzz3HpaLO4iA5l/RjQRST7r3JKBhfSVuEHKc16XpQ8uLY4hqnB1Tx6CWgtl7KV94= X-Received: by 2002:a67:e192:: with SMTP id e18mr1940128vsl.85.1641757718364; Sun, 09 Jan 2022 11:48:38 -0800 (PST) MIME-Version: 1.0 References: <137562d1-e6fe-4a68-b18b-abedef513fbd@www.fastmail.com> In-Reply-To: <137562d1-e6fe-4a68-b18b-abedef513fbd@www.fastmail.com> From: John Hendy Date: Sun, 9 Jan 2022 13:48:27 -0600 Message-ID: Subject: Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet To: Samuel Banya Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Host-Lookup-Failed: Reverse DNS lookup failed for 2607:f8b0:4864:20::92b (failed) Received-SPF: pass client-ip=2607:f8b0:4864:20::92b; envelope-from=jw.hendy@gmail.com; helo=mail-ua1-x92b.google.com X-Spam_score_int: -12 X-Spam_score: -1.3 X-Spam_bar: - X-Spam_report: (-1.3 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RDNS_NONE=0.793, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: Charles Berry Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Migadu-Flow: FLOW_IN X-Migadu-Country: US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1641757780; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:list-id:list-help: list-unsubscribe:list-subscribe:list-post:dkim-signature; bh=wD0qt+G6EiTNJL6mpIHqn6UlORMMS4f91i3dLaFq3AU=; b=JynRExrvbQBkbts1g4IWP/RwiBBLXZYW+uw3hutSjMMgJZU7QQoqhWZy7lE+9FF/HBxmmf FaJt4QvYpFX/j4yiCWNi2Gn6d3biEaeHvSER2GAZF4TMJSa6PK03m6+JAnq0QcSK0RIadX R4iQZL30uo+drU++3jXqeyEHCaquxLRtxaGc0Ys3ZN76E4yqQUjEvuzR04W226nIAXGpaz K93AQnNv4hX3LaKHOBD8iH6k64aws7fEllCMQ7ZmJD3aKKX0l83Dp5h451L7/6vazWPFXV UQQ3Tb7Y1coLLnyg2BoxCliiI43ZVFmKsDlsYQhVCCeZTLDV5P3eOwCcI/38vQ== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1641757780; a=rsa-sha256; cv=none; b=EVuXTO53f29hiV/xbHECCPBCFOKvgEtMo1ooXzkAXHX3wKUxRHhkFREsAAaReoknhxjpoY FDNscbgPUSMa+19IPuW9KMnelssd2uV3iCgsjOB0BNBOzcuo0aEgDuPvoS7cSj9VP9LL/I ndIH1FgHh4dtmyURJINRc93gbYKPd3LKKnTCSwFCC9SdoDJC+45Irtn9siVPd9kJCaoGrk IeBBLsjlKJqHuQeoNolP78Km8JV3AExM0WrATkAdaticM7osVAsn/gnpv5et0iTKD3iikG zI+qp9yP0lDF7pN/3I+viEBIn6b20qSM3b1L1zC4mGgHK1aMqai/bpcdvbdH4Q== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20210112 header.b=HTQrQ08K; dmarc=pass (policy=none) header.from=gmail.com; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Spam-Score: -9.31 Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20210112 header.b=HTQrQ08K; dmarc=pass (policy=none) header.from=gmail.com; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Queue-Id: 4E3892E29E X-Spam-Score: -9.31 X-Migadu-Scanner: scn0.migadu.com X-TUID: 7QVYJDOY6NXw On Sun, Jan 9, 2022 at 11:17 AM Samuel Banya 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 ' (sing= le quote)" (Obtained this formula via the docs, 'https://www.gnu.org/softwa= re/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 ref= er 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 =E2=80=98$3=3D= =E2=80=99, 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 Spreadshee= t [...] > 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=3Dvsum(@1$3..@2$3)::$4=3Dvsum(@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 spreads= heet? 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 cor= rect 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=3Dvsum(@1$3..@2$3)::$4=3Dvsum(@1$4..@2$4) #+begin_src R :var foo=3Dfoo jan <- sum(foo[1:2, 4]) feb <- sum(foo[3:4, 4]) result <- data.frame( month =3D c("jan", "feb"), total =3D 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 =3D=3D 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 mak= e it easier since one of my goals for the new year is to make recording fin= ances an easier process. > > I felt like I've been over complicating this, and figured someone probabl= y 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 >