Beancount getting started

Life Hacking
102

Thank you guys. This looks much more manual than I thought. A cron job and an additional code that runs the stuff and appends the file? It’s quite quirky for my taste. Fava has a transaction importer, it could also have a price loader in the GUI. It would be nice if it would download all missing prices.

Btw can you tell me what is the best choice for price date interval? EOD, EOW, EOM, SOM?

  • EOD seems like the simplest choice, but it may lead to a very long price file.
  • EOW seems like a middle ground, but I don’t like it because you usually look at data on a monthly basis, not weekly.
  • EOM is what I have, but I wonder if EOD would really be a problem (file size, performance), if not then EOD would be better
  • SOM (start of month): I was wondering, which date is relevant for monthly/annual calculations, is it the 31.12 or the 01.01? Like, if I want to measure m2m performance this year or for a given month, which dates does beancount/fava take for comparison?
1 Like
103

I use eod, I didn’t encounter any issues yet. If needed it would be easy to drop some off the price entries and only keep the relevant ones.

1 Like
104

OK, I guess if I figure out this price loader thingy then I will switch to EOD as well. I just need to load prices once a year, so I guess I just need to write a python script that will run bean-price for all days of the year for all the commodities that I have configured and bean-price will automatically figure out which prices are missing (the delta)?

Btw when I look at the source of of beancount, I get a headspin. It’s so impressive that a single human being can have all this knowledge and the time to do it all as a side project… Also your loaders are impressive, I look at the code and I would have no idea where to begin, even if I know python on a beginner level. Things like these make me humbly appreciate that someone is willing to pay me a lot for something that looks simple in comparison :smiley:

105

I understood you have no other option than that because if you have transactions of commodities and no price for them at the same day, fava will not be able to convert them properly if you want them all in CHF for example.

106

The price of commodities is included in a transaction. But you may have a point, I only have a few assets that I trade in CHF. VT is in USD and BTC in EUR. So if there is no xrate for that day, then how should the cost in CHF or return be measured…

But for sure it works somehow with total portfolio value. Maybe fava values an asset with the last available price? So if I have the price of 1 USD on 2020-11-30 at 0.95 CHF and then I buy 1 AAPL today for 100 USD, then fava calculates the value of 95 CHF? But for sure it would be more accurate to have the daily prices.

107

Afaik fava has two options. Convert to CHF which takes the latest available price info for both stock and currency and then there’s market cost which takes only the latest price. I often had situations that sell transactions of positions didn’t match the individual buy position prices (iiuc because IB doesn’t report which buy price a position had at sell time?) So I ended up deleting the prices of each asset in the individual transactions and just having the price entry for the specific transaction dates instead.

108

Actually, you want to match a particular (asset, transaction date) pair, and the price is irrelevant. I think, a couple of times I put just the whole amount of the trade and beancount derived the price from it. It’s especially helpful with cryptos, where you bought 0.5223 BTC and paid 2000 CHF, which gives a terribly long price. Then, when you want to sell, you have to specify which lot you are selling (e.g. BTC purchased at 2020-12-23)

1 Like
109

A couple of notes.

You need to differentiate between cost and price.

  • cost: tracked on lots, entered with the {} syntax
  • price: how much something is worth it, price entries or specified with the @ syntax for conversions

What I use is fifo for simplifying sells, https://beancount.github.io/docs/how_inventories_work.html that way I don’t have to specify my lots manually and it will just take the oldest ones.
That then also automatically allows to calculate the actual profits by comparing the cost of the lots vs how much I actually gain.

Price entries are only relevant for figuring out the current market worth of my stocks. As I do my analysis generally with year end, I could drop all price entries that are not at the year end (but like I mentioned, I have now a couple of years worth of daily price data and I don’t notice any slowdowns)

1 Like
110

Question: if I traded BTC in EUR, does it make more sense to fetch BTC prices in EUR or in CHF? Or does it make no difference?

111

Some tough hours, but I’ve managed to write a script that would load the whole year for some cryptos. Couldn’t have done it without your script as reference. I tell you, beancount is exclusively for programmers…

myprices.bean

2015-12-31 commodity BTC
  name: "Bitcoin"
  price: "CHF:coinbase/BTC-CHF"

2015-12-31 commodity ETH
  name: "Ether"
  price: "CHF:coinbase/ETH-CHF"

prices.py

#!/usr/bin/env python3

from datetime import date
from datetime import timedelta
from beancount import loader
from beancount.prices import price
from beancount.parser import printer
from pprint import pprint

def fetch_prices(dates):
    entries, errors, options = loader.load_file('myprices.bean')
    prices = []
    for dt in dates:
        dprices = price.get_price_jobs_at_date(entries = entries, date = dt, inactive = True)
        for dprice in dprices:
            prices.append(price.fetch_price(dprice = dprice))
    
    price_file = open(file = 'myprices2.bean', mode = 'a')
    printer.print_entries(entries = prices, file = price_file)

start_date = date(2020,  1,  1)
end_date   = date(2020,  2,  1)
dates = []
curr_date = start_date
while curr_date <= end_date:
    dates.append(curr_date)
    curr_date = curr_date + timedelta(days = 1)

fetch_prices(dates = dates)
2 Likes
112

Unfortunately yes. If there was some competent frontend engineer it could be turned into something very useful.

113

I love beancount and I use practically every day. I put the fees, bills, etc. by hand. I have the ledger synchronised in all my machines and is easy.

I use beancount for trading but in parallel I use PortfolioPerformance. Gives me much more information, graphics, TTWOR, IRR,etc. You can use Yahoo for prices or others, has 4 or 5 providers. Also has importers for several banks, IB, etc… PP is my main tool for investments.

114

I agree that beancount (and plaintext account in general) is quite heavily focused on programmers.

Before switching to beancount, my setup was gnucash + custom written app with a database for investments. With gnucash I learned about double book accounting, my custom webapp was able to do some investment analysis (like irr calculations, realized and unrealized gains, …)

With beancount I converted everything I had in gnucash and I was also able to rewrite/simplify a lot of my backend code of my webapp and actually use beancount as a calcuation engine. In the meantime that app doesn’t have any database anymore. So all my data is stored in beancount. This keeps my setup very nice. I use fava for importing/entering transactions and ad-hoc queries. A cronjob for fetching prices. For all the other reporting needs I use my custom webapp, which has exactly what I need and is able to retrieve values from beancount using its query interface.

115

By the way, I noticed you use mixedCase, not lower_case for variable names, which is not in line with PEP 8 :smiley: (sorry, at work I obsess about coding guidelines and scold other developers)

116

What can I say, I’m a Java programmer by day, it’s hard to drop some common patterns when switching languages :wink:

117

I was able to import prices for 5 years, but I got stuck at 2019-09-15, yahoo throws an error, the response does not contain the “timestamp”. Where do you fetch your ETFs and currencies? The yahoo finance had it all…

118

Alphavantage, exchgangeratesapi and interactive brokers.

2 Likes
119

By the way, is any one aware of an existing conversion tool from Beancount to Portfolio Performance?
Ideally one would keep all transactions in bean files, and use PP for visualisation and reporting of investments.

120

Very good question… :thinking:

I was working for exports to DB’s, like Postgres but I never thought about PP… And I use everyday!

For me is not useful anymore because I have all done from the beginning but if I export a CSV from PP I see a “standard” structure like:

Date;Type;Value;Transaction Currency;Taxes;Shares;ISIN;WKN;Ticker Symbol;Security Name;Note

And with data (example):

 2019-11-05T15:30;Buy;-6,517.00;USD;;;;;CL;CL - Colgate-Palmolive Company;
 2019-11-05T16:00;Buy;-6,500.00;USD;;;;;JNJ;JNJ - Johnson & Johnson;
 2019-11-07T00:00;Dividend;136.74;USD;24.13;640;;;AGG;AGG - iShares Core U.S. Aggregate Bond ETF;

With a:

 bean-query -f csv  -o $CSV_FILE $BEAN_FILE 'select date, payee, narration, account, number, currency, tags'

You could export the data from Beancount and working a bit on it, adapt it for PP.

PP has lots of import possibilities and plugins. I don’t speak German but in their forum there are lot of questions about import/export data…

121

Hey everyone, creating an export to PP is a really good idea. I am currently exporting my beancount to influxdb and visualizing with Grafana. Unfortunately, I am not handling equities well with Grafana yet, but I had plans to re-create some of the PP views into Grafana but obtaining the portfolio from beancount.

One thing that is annoying me is that I did not find a good way to read beancount data without first exporting to CSV and then reading the CSV. If you are doing it in some way that does not need to first export to CSV, please share :wink:

In case it helps this is how I am currently the generating the CSV on my script which I then read and export to influxdb:

from beancount.utils import misc_utils
from beancount import loader
from beancount.query import shell
from beancount.parser import version

import csv
import sys

# A function that the beancount.query shell module needs
def load():
    # sorry, config is defined elsewhere in my code, you want to put the path to your main beancount file here.
	beanfile = config.fi_path+config.bean_path
	errors_file = sys.stderr
	with misc_utils.log_time('beancount.loader (total)', logging.info):
		return loader.load_file(beanfile,
    							log_timings=logging.info,
    							log_errors=errors_file)

def bean_query_to_csv(outfile,query):
    """
    outfile - path of the CSV file to write
    query - a beanquery string, i.e: "SELECT date, account, sum(number) as Total, currency FROM year WHERE account ~ 'Income' GROUP BY date, account, currency ORDER BY date, currency"
    """
	with open(outfile, 'w', encoding='utf-8') as csvfile:
		shell_obj = shell.BQLShell(False, load, csvfile, 'csv')
		shell_obj.on_Reload()
		shell_obj.default(query)

As an example, this is how the expenses graph exported looks like in Grafana:

Screen Shot 2020-12-24 at 1.21.40 PM
Screen Shot 2020-12-24 at 1.21.40 PM2547×1059 138 KB

I filtered taxes out of this graph so I can control what I am spending money on every month. Goal is to keep it lower than 4k for the moment.

I made some other graphs to track other things, like saving rates and income but I keep changing the graphs as I don’t know yet what is really useful :roll_eyes:

What financial metrics do you track every month? I am taking ideas on what to implement with Grafana :slight_smile:

2 Likes