Keeping data in Spreadsheets, but using beancount

[Chary Chary]

Dear all,

can you please comment on the following my idea.

I keep all my data in Excel spreadsheets and I am kind of hesitant to move it all to text format. 

Let us say for simplicity I have the following spreadsheets

• Euro paying account

• USD paying account

• Cash spending spreadsheet (for all currencies). I use https://toshl.com/ for this.

In each of these spreadsheets I use the same set of categories to assign to transactions (e.g. grosseries, entertainment etc).

So my idea is to keep data in these spreadsheets, but to use beancount to pull data together, and analyze this all together. I assume, that I may be adding some limited  transactions in traditional beancount textual format (e.g. to link transfers between accounts)

To achieve this I propose to write plugins, which will read Excel file and return entries in beancount format.

Any thoughts on this?

 

[Uwe Ziegenhagen]

Well, I would use the pandas library to read the Excel files. Then you just need to iterate over those rows and create the Beancount syntax. I have started similar work by using pandas to store the data I had read from Quicken files, see the github https://github.com/UweZiegenhagen/PyQIF-Parser. Your task may be a bit easier as you do not have to parse the QIF file first, you already have the Excel. For you the following function inside PyQifParser.py could be interesting

def to_beancount(self, outputfile):

"""

Exports the transactions, accounts, classifications and

categories from pandas dataframe into an Excel-file

@TODO: waits for detailed specifications, currency is still hardwired

"""

self.transactions['bcdate'] = self.transactions['Date'].dt.strftime('%Y-%m-%d')

with open(outputfile, "w", encoding="utf-8") as writer:

for entry in self.transactions.index:

writer.write(self.transactions['bcdate'][entry] + ' * "' + str(self.transactions['Description'][entry])[:50] + '"\n')

writer.write('\t' + self.transactions['Category'][entry] + '\t'*10 + str(self.transactions['Amount'][entry]) + ' EUR\n')

writer.write('\t' + self.transactions['Category'][entry] + '\t'*10 + str(-1 * self.transactions['Amount'][entry]) + ' EUR\n\n')

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4db1ccc9-6e45-43d0-88ca-762d3868f0da%40googlegroups.com.

--

Dr. Uwe Ziegenhagen

0179-7476050
<http://www.uweziegenhagen.de>

[Chary Chary]

Uwe Ziegenhagen,

thanks!

I think you are talking about converting files to beancount text format, but I wanted to create a plugin which dumps data directly into the RAM in a format of beamcount transactions objects.

To unsubscribe from this group and stop receiving emails from it, send an email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4db1ccc9-6e45-43d0-88ca-762d3868f0da%40googlegroups.com.

[Martin Blais]

I wouldn't suggest that; probably better to produce the text file from your spreadsheets via a script.

Also, you can use xlrd to read Excel spreadsheets.

Furthermore, it's possible to use Google sheets as well if you prefer all this stuff online, there's some examples in the codebase.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3ed8b8ec-100c-47cd-ac5f-0f4373506af7%40googlegroups.com.

[Chary Chary]

Martin,

why do you not advice this? For performance reasons? You mean the beancount text parser will work faster, than code, which reads Excel and outputs beancount transactions? 

The reason I want to keep source data in spreadsheet is that because it is easier for me to work with them. I can filter things, re-assign categories to massive amount of rows at the same time etc.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3ed8b8ec-100c-47cd-ac5f-0f4373506af7%40googlegroups.com.

[Martin Blais]

On Mon, May 25, 2020 at 4:56 AM Chary Chary <char...@gmail.com> wrote:

Martin,

why do you not advice this? For performance reasons? You mean the beancount text parser will work faster, than code, which reads Excel and outputs beancount transactions? 

Beancount does a lot of stuff to the stream of transactions (e.g. sorts them in a particular order). You want the parser + plugins to run on it, and not have to redo some of the work from https://github.com/beancount/beancount/blob/master/beancount/loader.py in your own code. Creating a home-made stream of transactions (from your script, not coming from the loader) and then calling Beancount library functions on it may not work properly. For example, some code in the loader guarantees that every account has a corresponding Open directive and some of the library functions rely on this. There are a number of invariants like that; in the next version I'll make that contract explicit and better defined.

The reason I want to keep source data in spreadsheet is that because it is easier for me to work with them. I can filter things, re-assign categories to massive amount of rows at the same time etc.

That's fine. Just have your script generate text and then have Beancount process that text instead of creating your own stream of transactions.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e3fe2d27-6178-493c-ac2d-09c2c5aebc75%40googlegroups.com.