From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp10.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms0.migadu.com with LMTPS id eOcAO2wy22HuPwEAgWs5BA (envelope-from ) for ; Sun, 09 Jan 2022 20:07:24 +0100 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp10.migadu.com with LMTPS id GCvPM2wy22EEhQAAG6o9tA (envelope-from ) for ; Sun, 09 Jan 2022 20:07:24 +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 49B2B32BF6 for ; Sun, 9 Jan 2022 20:07:24 +0100 (CET) Received: from localhost ([::1]:35768 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1n6dXH-0006qE-7Y for larch@yhetil.org; Sun, 09 Jan 2022 14:07:23 -0500 Received: from eggs.gnu.org ([209.51.188.92]:53460) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1n6dVq-0006pH-Uj for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 14:05:58 -0500 Received: from [2607:f8b0:4864:20::1030] (port=55219 helo=mail-pj1-x1030.google.com) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1n6dVo-0005ME-RZ for emacs-orgmode@gnu.org; Sun, 09 Jan 2022 14:05:54 -0500 Received: by mail-pj1-x1030.google.com with SMTP id oa15so10639844pjb.4 for ; Sun, 09 Jan 2022 11:05:45 -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; bh=MMl3WLSxNM8RD8pEpVHzeAi+ofFqaYmwge/eAExhdfY=; b=e360POQCh9oNBl9OaiFsZjzhcAZRC8s32vkoyah+IzOoYwllW85e2g6aMg3nqgrXon la3HCaPbvwdwvsFOEVD7ELfb0eUtOOFlMAq/EcWJ8f2wG3a8/GugglifyUhAUuex8cN3 fYPvTAvYlr31K/hVXoAvqihQfVkPoUs4lj6CqW4haMFwe+mw+88GA2itfTju2kTagzVs BG48LtRCAjn3NlAA9VDTcgTOVoUvyr96zYCc8uFGDdrMY5h7GKXOXOboUBtNET0KwwYp CsSnPO+BD3HVWr/MMJCWZWvh9SHqY9JSm0h1RN9DQM6vShDxqq1+Efn3DIQ1Ix7DUBQW VmnA== 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; bh=MMl3WLSxNM8RD8pEpVHzeAi+ofFqaYmwge/eAExhdfY=; b=YiVTsA/T5DqZofLTcZ7ZFsOpsGgVq286icE7QJhvC7LQxdAHJyi5CwY35w3BKxp67y B5uvghw/OJARMRrkfaUR2g384ibjXzZRtVxudePWAnOWdjFRwV8V5JHeGnY50uLKdVqA +kmAnPW40mgA+yB4WFzqquFy4B106bY5evH12AAuE3Bl22ONq12HYPS225uw/mkE5165 kHUlqbVT1/giGaLAtzFDA6H8MDkJMbnnf8VYUXJhUCQyPuskyrY9lkhbnE25LJrlaO2Z VvLdvPLlt60lm0p2UwI/kw0QhM5XPCIstvAdKUokeBN1AkL3cVkYugLtyuVnCC4d6pUZ jOqA== X-Gm-Message-State: AOAM532r7YVgA4usGH7G7T4BmrDKq6zCC7CrQjMutMwA+JpuLlibSTiU 5JBOFZhdmeoxfpfcpSgZfnopZrClz62tEFfVtJU= X-Google-Smtp-Source: ABdhPJw+gVXe+3bt81p4iuHzBlaOtB1LCdv5XRXawphNpV51mBOWPCha1bYhP0FLm3K0NL1g8HGqZPLyY3/SF/Dh+EM= X-Received: by 2002:a17:902:f693:b0:149:e52b:1733 with SMTP id l19-20020a170902f69300b00149e52b1733mr19441173plg.5.1641755144536; Sun, 09 Jan 2022 11:05:44 -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: Ken Mankoff Date: Sun, 9 Jan 2022 11:05:33 -0800 Message-ID: Subject: Re: Question Regarding Creating Workflow For Automatic Formulas For Finance Based Org Spreadsheet To: Samuel Banya Content-Type: multipart/alternative; boundary="00000000000032c68605d52ae8dd" X-Host-Lookup-Failed: Reverse DNS lookup failed for 2607:f8b0:4864:20::1030 (failed) Received-SPF: pass client-ip=2607:f8b0:4864:20::1030; envelope-from=mankoff@gmail.com; helo=mail-pj1-x1030.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, HTML_MESSAGE=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=1641755244; 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:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:dkim-signature; bh=MMl3WLSxNM8RD8pEpVHzeAi+ofFqaYmwge/eAExhdfY=; b=TPRgFdjdaT1laJYuFdkDkl+CVmocuCgxxijm56BeJMGDxwghCXzn+jZskltur58Zv08pJ+ CFyt9C/3Snx/5ZjrPa9lqLv9KuO9CEd131Jhrh/X57Jhy1bnPJBm+t1EOetenTPsTz0Q1c wVyhYiMJO0+9PE/QGbSRNZlj/tFkmGd+TZdgdLLaC1yWt2CGa7puP7oXiq9R+LsqGicOOv g1ztjj6Cvv2AMTJH75+KseTMwQjdHYzRnYJ08PKird2tjLOXzjZpAOzzTPdzKzhZkeM8FU Da/5elZqs+qjiG2s6IwOD7FswJndjdus6KfQwaEPokLVq6ROirpFWbr/SZNtAQ== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1641755244; a=rsa-sha256; cv=none; b=PJdn2nWjfuXt5HwTfGxkVvEiMMa++Nlo0mrZv+uEUrMfRk3eYzCK9DEPNs2j28Z6qsNelA fWHzW17jtjYBmziUVt/gBcR/j4+MGX3aMDZR60uNJt81/GXIEvU1coB+t0BYdcj1n1aeZX JyxW/D1DFQ136pS3obWmHFWni8Wbx1PVtEOw2VovUH0bmIktOtm2U3MtltfjeV//AtOvvV oX0iTKyd8m6PbTNUX0rAJix2cL7IpNVSlQDdoaNbVpPQwfuRvsaVlDTW/nY1/cCiEZfCn1 E+Azhwji23agHLsQQ0iV/8tEtg2Nn1fUSnaIvwrOPDCQeVSOXJAUiL5qKC0qIA== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20210112 header.b=e360POQC; 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: -4.31 Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20210112 header.b=e360POQC; 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: 49B2B32BF6 X-Spam-Score: -4.31 X-Migadu-Scanner: scn1.migadu.com X-TUID: BRf4EmO0V49F --00000000000032c68605d52ae8dd Content-Type: text/plain; charset="UTF-8" Hi Sam, Have you looked into Org Ledger? -k. Please excuse brevity. Sent from tiny pocket computer with non-haptic feedback keyboard. On Sun, Jan 9, 2022, 09:18 Samuel Banya wrote: > 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 > > --00000000000032c68605d52ae8dd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Sam,

Have= you looked into Org Ledger?

=C2=A0 -k.=C2=A0

Please excuse brevity. Sent from tiny pocket computer with non-hapti= c feedback keyboard.

On Sun, Jan 9, 2022, 09:18 Samuel Banya <= ;sbanya@fastmail.com> wrote:<= br>
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 o= nly annoying thing I want to somehow figure out this year is how to make th= e process of updating the formula for a given month less tedious, so I am w= ondering if anyone could help me figure out a better workflow for doing thi= s.

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&= #39; to highlight everything, and use 'C-c |' to convert the range = into an org table
  • Open up the existing running total org mode s= preadsheet in a separate buffer
  • Copy over the converted org mod= e table data from the new .csv buffer and paste it into the running org mod= e 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:

| 02/06/2022 | Example Random Expense=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |= =C2=A0=C2=A0 | -2061.67 |
|= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | EXPENS= ES Feb 2022 vsum(@3$3..@4$3) vsum(@3$4..@4$4)=C2=A0 | 0 | -4123.34 |
|------------+-----------------------------------------------------= -+---+----------|

Formulas Presented In the= 3rd and 4th columns for Row 5:
# Column Formulas<= br>
$3 =3D vsum(@1$3..@2$3)
$4 =3D vsum(@1$4..@2$4)=

Formulas Presented In The 3rd And 4th Colu= mns For Row 6 (NOTE: Notice how I can't make them unique = for each row for some reason):
# Column Formulas
$3 =3D vsum(@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 scen= ario?
  • Why is it that when I use "C-c ' (single quote)&= quot; 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 curren= t date time stamp and figure out the month's row at the bottom of the s= preadsheet 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 as= k 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
    <= div>
--00000000000032c68605d52ae8dd--