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 2IEdAbEY22GfswAAgWs5BA (envelope-from ) for ; Sun, 09 Jan 2022 18:17:37 +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 wNJMObAY22G2KwAAauVa8A (envelope-from ) for ; Sun, 09 Jan 2022 18:17:36 +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 84A4B2C01D for ; Sun, 9 Jan 2022 18:17:36 +0100 (CET) Received: from localhost ([::1]:43652 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1n6bp1-0004a9-MU for larch@yhetil.org; Sun, 09 Jan 2022 12:17:35 -0500 Received: from eggs.gnu.org ([209.51.188.92]:58038) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1n6bVC-0006EL-Oc for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 11:57:06 -0500 Received: from out4-smtp.messagingengine.com ([66.111.4.28]:43999) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1n6bVA-00048l-7g for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 11:57:06 -0500 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 92C595C0083 for ; Sun, 9 Jan 2022 11:57:00 -0500 (EST) Received: from imap49 ([10.202.2.99]) by compute3.internal (MEProxy); Sun, 09 Jan 2022 11:57:00 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fastmail.com; h= mime-version:message-id:date:from:to:subject:content-type; s= fm1; bh=45xJvwcqo0eOUJPu0MiEUwGOcgg6oiWJ7WimjZc3Lnw=; b=lP/wPBFn XHPuu19YaBnW80/MHWIex7nYEac+K5fsb8K0NLJAmmht1EP/pdL/lLnyhCwKTeP2 xS7thr05QqfTTPEMR3epgRp1MtRSwa4j8QED/1ga5yxpm6s4MwVS21y5CM5ni+3v A84PCvzgEP3DZ8w/dFdDjyH3VcrRFkAe2Z+rZqN4vK18EvT8LR7ZlQfBL0fWBw98 h3r1c0o3B2MyIdp4eBunZ2F7fD/uvho8/9HlfD7qnybmOWQt6QFmqSwkiwp0lYXo jefoyAW5cBS/TYw2crqmiUyC98DIcYAQk8hy29JQgVFk3avBnzcmahK1N2/78BLJ N1N+ckkFnP5oeQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-type:date:from:message-id :mime-version:subject:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; bh=45xJvwcqo0eOUJPu0MiEUwGOcgg6o iWJ7WimjZc3Lnw=; b=fqZmRsPUuLqPScesm2N1sUWfyLBfIWnq3zicQWnegvnZD 2rKaXwZa5S43tNL4XNvNUVC+Ym6+ufLszvkQiqxBvyvxNXCwrgwXyJxfnFRNnvxq OAlGbq6oRqYjQavsTPof/4KH5hvUROOX34/mWSQR4Iy/jrkWFiP7JbfH2n+MnoSY g850QPtIBlNWSri0M1AqOQ7A850whNta6hBMm0WqFahKhLS/CiSr3aDVON6INPYh lHLFhqOY/LqlnFvRUbop+9tzFgjoOkGKBwsCxpqCLepsP/1PegNZs1swH48QBHRx x+9gVqzqfgYoMk1TSyVI1g8b9uuijpYyU8Ir7rMSA== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvuddrudegkedgleejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvufgtsegrtderre erredtnecuhfhrohhmpedfufgrmhhuvghluceurghnhigrfdcuoehssggrnhihrgesfhgr shhtmhgrihhlrdgtohhmqeenucggtffrrghtthgvrhhnpefggfdvfeeigfefvdejveetgf ekkeehvdekveehfeeutedvfeefuefhtedtuefgjeenucffohhmrghinhepshgrmhgvrdho rhhgpdhgnhhurdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomhepshgsrghnhigrsehfrghsthhmrghilhdrtghomh X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id 6420DF60075; Sun, 9 Jan 2022 11:57:00 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.5.0-alpha0-4526-gbc24f4957e-fm-20220105.001-gbc24f495 Mime-Version: 1.0 Message-Id: <137562d1-e6fe-4a68-b18b-abedef513fbd@www.fastmail.com> Date: Sun, 09 Jan 2022 11:56:05 -0500 From: "Samuel Banya" To: "Charles Berry" Subject: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet Content-Type: multipart/alternative; boundary=4949aec5ec4e4612b40b40673a3b8cf2 Received-SPF: pass client-ip=66.111.4.28; envelope-from=sbanya@fastmail.com; helo=out4-smtp.messagingengine.com X-Spam_score_int: -27 X-Spam_score: -2.8 X-Spam_bar: -- X-Spam_report: (-2.8 / 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, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham 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: , 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=1641748656; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type:list-id:list-help:list-unsubscribe: list-subscribe:list-post:dkim-signature; bh=45xJvwcqo0eOUJPu0MiEUwGOcgg6oiWJ7WimjZc3Lnw=; b=FaGrwFUJBSoYYB7Y4Co7kNKN6jfAB/aFdPdmeNqmjFQU48ZEjk8qq9ql830tLSDFiryx3C 3hLH+mliz6asao2cXCRTZnKW9j0Jqrcybv3Bhokl0E0rrapdLSrmDpAYFitI6SvFfQWu/5 HHujc823Wr6JNbKFPQUyLfxsAsDESVwgtfXXL18271ev3qfBucXmkVif5E92da9OKeDfmD UVjTXuYt6Xp+gcOMrLqLP08/uwdDNFzQXIcitqUOho6AoedZ5rEBh3wBB7E3AG7vuQhc5+ 38WVzwWw0VId5JQDq3/3NzIxQWpvxIgHz3RkYUV01CvSyERmtBrUgZnbQZfw3Q== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1641748656; a=rsa-sha256; cv=none; b=bP/otGowy4s0cAhFnLuaBw2mwWVj8YTWs/ZfY93l7VJdd9F16BSIp6ilz2nsW9OImG821L ddiu1DPZXPymKVtXV7sEYTQaJD9zmyDBaS76SsrSqvx4mEyhQHIzaHe9MZ2Mrz6y65DKwO /kjH5Lkvi19A7/EJriuyp9gq26+MJMqdy8pJRa592tkmNUcNHC8h+z8jBahTrqm1y1wU2u Fkfx/MBKym5uL/LYds7Qm6JpQ2znT2FHSHBGc/NQcQCkr341R+r+OArGCJAbC8DgTacgLI IZdRedPvonz2NA8QN2gZ4tluLO71mUK9b2CApX+lodpQzEgrv8y2YFG0U2CLkg== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=fastmail.com header.s=fm1 header.b="lP/wPBFn"; dkim=pass header.d=messagingengine.com header.s=fm1 header.b=fqZmRsPU; dmarc=pass (policy=none) header.from=fastmail.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: -4.40 Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=fastmail.com header.s=fm1 header.b="lP/wPBFn"; dkim=pass header.d=messagingengine.com header.s=fm1 header.b=fqZmRsPU; dmarc=pass (policy=none) header.from=fastmail.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: 84A4B2C01D X-Spam-Score: -4.40 X-Migadu-Scanner: scn1.migadu.com X-TUID: 8KuYSVucbEd3 --4949aec5ec4e4612b40b40673a3b8cf2 Content-Type: text/plain 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 --4949aec5ec4e4612b40b40673a3b8cf2 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
Hey there,
<= /div>

So I've been managing my finances via an org do= c that basically has tables that list all the expenses from my bank acco= unt, 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 tedi= ous, so I am wondering if anyone could help me figure out a better workf= low for doing this.

Here's My Cur= rent 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 i= nto an org table
  • Open up the existing running total org mode= spreadsheet in a separate buffer
  • Copy over the converted or= g 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 a= nd column values in the spreadsheet
  • Find the existing formul= a 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 t= o 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 ba= ck to the spreadsheet.
  • I then hit 'C-c *' to update the tabl= es present.
  • The problem with this is that when I attempt to = do this function for each separate calculation row, both of the bottom c= alculation rows now only refer to a single formula for some reason.
    <= /li>

For Reference, Here's A Modified Version= Of My Running Finance Spreadsheet (Modified Without Actual Values Or Re= cords For Personal Reasons) (NOTE: Sorry that the paste is terribly alig= ned --> 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 mont= h since I can never remember the syntax personally since its flipped fro= m typical Excel syntax of using row and column --> it uses column the= n row like an older calculator)

|------= ------+------------------------------------------------------+---+------= ----|
| 01/03/2022 | Example Rent Expense   = ;            = ;            = ;      |   | -1061.67 |
| 01/04/2022 | Example Food Expense      =             =             =    |   | -1061.67 |
| 02/05/2022 | Exa= mple Utility Expense        &nbs= p;           &nbs= p;         |   | -2061= .67 |
| 02/06/2022 | Example Random Expense  &nb= sp;           &nb= sp;           &nb= sp;    |   | -2061.67 |
|--------= ----+------------------------------------------------------+---+--------= --|
|         = ;   | EXPENSES JAN 2022 vsum(@1$3..@2$3) vsum(@1$4..@2$4) = ; | 0 | -2123.34 |
|      &n= bsp;     | 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 R= ow 5:
# Column Formulas
$3 =3D vs= um(@1$3..@2$3)
$4 =3D vsum(@1$4..@2$4)

<= /div>
Formulas Presented In The 3rd And 4th Columns For Row 6 (NOTE: Notice how I can't make them unique for each row for s= ome reason):
# Column Formulas
$3 =3D vs= um(@1$3..@2$3)
$4 =3D vsum(@1$4..@2$4)
Main Questions Regarding Formula Issues:
  • Why can't I use individual row formulas in this scenario?
    <= /li>
  • Why is it that when I use "C-c ' (single quote)" the row formula= s are the same for completely separate rows

=
My Main Workflow Questions Include The Following:
<= ul>
  • Is there a way I can maybe automate the initial steps of converti= ng 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 existi= ng formula for the given month if Emacs would somehow know the current d= ate time stamp and figure out the month's row at the bottom of the sprea= dsheet accordingly to update the correct row?

    • Anyway, I know my question might be a bit ambiguous and most like= ly will involve using 'F3' to record macros, but I figured i would ask t= o maybe make it easier since one of my goals for the new year is to make= recording finances an easier process.

    I f= elt 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

    --4949aec5ec4e4612b40b40673a3b8cf2--