Beancount for cryptocurrencies: Portfolio value with API prices

[Vonpupp]

Hi,

Congratulations on beancount. I am new to it but I think it is an amazing software, therefore I would like to take the most advantage of it. This is actually my first message on the forum.

I would like to use beancount with cryptocurrencies. I have may doubts but I prefer to take one step at a time. What I would like to be able to do first is to be able to see the my portfolio value at the present time. For instance, let's assume the following file:

; -*- mode: org; mode: beancount; coding: utf-8; fill-column: 400; -*-

;

; Cheatsheet

; https://docs.google.com/document/d/1M4GwF6BkcXyVVvj4yXBJMX7YFXpxlxo95W6CpU3uWVc/edit

option "title" "Crypto"

option "operating_currency" "ETH"

option "operating_currency" "GAS"

option "operating_currency" "LTC"

option "operating_currency" "NEO"

plugin "beancount.plugins.book_conversions" "Assets:Crypto:ETH,Income:Capital-gains"

;* Equity

;2017-11-01 open Equity:Opening-Balances

* Account declarations

; Account1

2017-08-18 open Assets:Crypto:ETH:Account1             ETH

2017-08-18 open Assets:Crypto:LTC:Account1             LTC

; Account2

2017-08-18 open Assets:Crypto:ETH:Account2             ETH

; Exchange

2017-08-18 open Assets:Crypto:GAS:Exchange             GAS

2017-08-18 open Assets:Crypto:NEO:Exchange             NEO

2017-08-18 open Expenses:Crypto:ETH:Balance            ETH

2017-08-18 open Expenses:Crypto:GAS:Balance            GAS

2017-08-18 open Expenses:Crypto:LTC:Balance            LTC

2017-08-18 open Expenses:Crypto:NEO:Balance            NEO

* Income

2017-08-18 open Income:Capital-gains

* Expenses

2017-08-18 open Expenses:Financial:Commisions:USD

* Transactions

2017-11-19 * "Opening balance: Account1"

    Assets:Crypto:ETH:Account1                5.12561083 ETH

    Assets:Crypto:LTC:Account1                7.58990978 LTC

    Expenses:Crypto:ETH:Balance              -5.12561083 ETH

    Expenses:Crypto:LTC:Balance              -7.58990978 LTC

2017-11-19 * "Opening balance: Account2"

    Assets:Crypto:ETH:Account2                3.968560 ETH

    Expenses:Crypto:ETH:Balance              -3.968560 ETH

2017-11-19 * "Opening balance: Exchange"

    Assets:Crypto:NEO:Exchange                4 NEO

    Assets:Crypto:GAS:Exchange                0.18651499 GAS

    Expenses:Crypto:NEO:Balance              -4 NEO

    Expenses:Crypto:GAS:Balance              -0.18651499 GAS

I am not experienced with accounting and english is not my primary language, but I guess the file is in "accounting good shape" and it makes sense somehow. If not please let me know.

Now let's go to the point. Assuming that file I would like to be able to issue a command on the command line and be able to see the portfolio in USD or EUR at the present time value. I can query most of the coins using a pip package, the question is, how I do that? I have read the "Prices in Beancount" [1] google docs file, however there is no example and it would be very useful to grasp a bit better how everything ties up. Is there any example of fetcher module so I can build mine on top of it? Or if you are aware of any module for prices that works with crypto that I could use out of the box it would be even better.

Please note that each coin might be on an exchange account or in a wallet (account1 or account2). I would like to get the overview of this sum, for instance when I refer to "ETH" I would like to see as it appears on the balance, but at the current USD or EUR price, and so for the other currencies.

From what I understood about the prices document, the prices will be fetched and saved on the beancount file automatically. I would like to do this automatically on a weekly basis, so I guess cron is my friend here. I guess it is wise to use a separated file and link them together somehow.

I would also like (if possible) to have this on a web interface like fava or similar if possible. I guess this might work automatically once the prices are correctly fetched.

Could you please point me into the right directions to get all these things working properly?

Thank you very much.

[1]: https://docs.google.com/document/d/1thYRAMell_QT1Da1F_laprSs6BlROZjyK_h3V8qHW9c/edit

[Martin Blais]

Search the mailing-list for bitcoin or BTC, you'll find related discussions.

Generally the problem with tracking bitcoins with Beancount is that Bitcoins tend to be used BOTH as a speculative "investment" and as a currency (e.g., to buy things).

If you want to think of it like an investment you want to track cost basis to compute returns... which makes it more difficult to use as a currency.

If you use it like a currency,  then you don't use cost basis but then you can't compute returns against USD (*) or your home currency.

Anyhow, there's no great solution that buys you the best of both worlds.

To be frank, there hasn't been much thought on making this a first-class use case, Bitcoin is still very much an experiment, and personally I think the blockchain has no future in its incarnation as a currency (but that is a much longer discussion). Beancount does work for it, but as mentioned, the best compromise is to segregate the accounts where you hold it for value speculation and the accounts where you might use it against expenses... separate accounts, one which tracks returns, the other which doesn't.

As far as creating your own price fetcher, you have to use the source, there isn't much of it and it's all under here:

https://bitbucket.org/blais/beancount/src/tip/beancount/prices/

(*) Maybe one day I'll implement the "currency accounts" method automatically so you can have a bit of tracking of profits without explicitly entering cost basis, at least in aggregate. I mean this: http://www.mathstat.dal.ca/~selinger/accounting/tutorial.html

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/88c1a28b-28ed-47fe-8a6d-489823f24bd0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vonpupp]

Thank you for your fast reply Martin,

My objective by using beancount on crypto is to be able to track profit and losses. Eventually I trade and I would like to be able to record that also. I guess this falls into the speculative investment category. I have looked up in the forum and in fact I build this test file from that.

I could sketch a rough version of a module that is able to fetch the values:

(.env3) > $ bean-price -e USD:coinmarketcapsource/ETH --no-cache

2017-11-20 price ETH                    358.93000000000000682121026329696178436279296875 USD

                                                                                

(.env3) > $ bean-price -e USD:coinmarketcapsource/NEO --no-cache 

2017-11-20 price NEO                    40.57000000000000028421709430404007434844970703125 USD

                                                                                

(.env3) > $ bean-price -e USD:coinmarketcapsource/GAS --no-cache 

2017-11-20 price GAS                    24.309999999999998721023075631819665431976318359375 USD

What it is still not clear is how this ties up to have a general overview of the portfolio. From what I understood on the the bean-price documentation, I should be able to do something like:

(.env3) > $ bean-price portfolio.beancount

And expect something to happen but nothing happens. Could you please point me into the right direction?

Thank you very much.

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

[Vonpupp]

I got it working thank you in any case.

I might continue this thread in the future if needed.

Thanks.

[Martin Blais]

If you want an overview, you can use an SQL query (using bean-query), use bean-web, use better: use Fava, or do like I do: export all your holdings to a CSV file and upload them to an existing sheet in a Google spreadsheet, where I have other sheets that feed upon that one and do various aggregations and plot graphs of pre and post tax amounts and exposure-per-asset-class and all that type of stuff.

I have a little Makefile with these commands in it:

#!/usr/bin/env make

BEANCOUNT = <path-to-your-beancount-filename>

...

upload:

        $(HOME)/beancount/experiments/upload/list_holdings.py $(BEANCOUNT) > $(PWD)/blais.assets.csv

        upload-to-sheets -v --docid="<your-document-id>" $(PWD)/blais.assets.csv:Upload

Instead of "list_holdings.py" I could (probably) use an SQL query and export to CSV (haven't tried yet).

"upload-to-sheets" comes with Beancount and replaces the contents of an existing sheet in a spreadsheet (in this case the "Upload" sheet).

Enjoy,

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/a778178d-448e-4fed-a8c3-ffd29034e6ec%40googlegroups.com.

[Vonpupp]

Thank you Martin for the nice ideas.

I have some related questions (underlined to try to ease your readability).

First: Here is an output example of the prices:

2017-11-20 price ETH                    367.93000000000000682121026329696178436279296875 USD

2017-11-19 price EUR                              1.173155 USD

2017-11-20 price GAS                    23.3599999999999994315658113919198513031005859375 USD

2017-11-20 price GNT                    0.2210999999999999909849890400437288917601108551025390625 USD

2017-11-20 price LTC                    72.9899999999999948840923025272786617279052734375 USD

On my price source provider I have something like this at the end:

...
price = D(result[symbol][base_currency])
...
return source.SourcePrice(price, time, base_currency)

I wonder, is there a way to limit the decimal places? I tried with the Python Decimal's get_context (if I remember correctly).

Second: In general the prices source I made seems to work, so I would like to refactor it to a python module, so it is more maintainable and easy to install for others to use, if needed. The big issue is how do I use a python package as a price source provider?

Currently I have a file on the same folder of the beancount files called cryptocompareusd.py and it works fine (I added to the PYTHONPATH env var), however I have created a package using cookiecutter and the structure is as follows:

.

├── beancount_cryptocompare

│   ├── cryptocompareusd.py

│   └── __pycache__

│       ├── cryptocompareusd.cpython-36.pyc

│       └── __init__.cpython-36.pyc

├── beancount_cryptocompare.egg-info

│   ├── dependency_links.txt

│   ├── not-zip-safe

│   ├── PKG-INFO

│   ├── requires.txt

│   ├── SOURCES.txt

│   └── top_level.txt

├── ...

├── setup.cfg

├── setup.py

I am not sure how to use the provider once on a python package. If I install the package described with `pip install --editable .` and I `import beancount_cryptocompare.cryptocompareusd` it works, however when I use the namespace on the price attribute it doesn't work, example:

2017-11-19 commodity BTC

  export: "Crypto:BTC"

  name: "Bitcoin"

  price: "USD:beancount_cryptocompare.cryptocompareusd/BTC"

Third: Is there any way to parse within the `get_latest_price` of the source.Source the external currency? I mean the first part before the ":", in the above example USD. (same as when using the command: `bean-price -e USD:cryptocompareusd/ETH`).

I am using a pip egg that supports querying the price of a cryptocurrency in another currency (USD, EUR, BTC, etc), so what I first thought is to define multiple "price:" attributes for each currency, however I don't know if that makes sense. I haven't yet tried that. What would be the right way to do this?

Fourth: Related to the last issue, I have found a query to get the current portfolio values using the prices:

bean-query portfolio.beancount "\

select account, convert(sum(position), 'USD') \

group by account \

order by account \

"

This works awesome as expected (thank you!):

           account            convert_su

----------------------------- ----------

Assets:Crypto:Exchange1:BTS        10 USD

Assets:Crypto:Exchange1:CVC        10 USD

Assets:Crypto:Exchange1:STRAT      10 USD

Assets:Crypto:Wallet1:ETH          10 USD

Assets:Crypto:Wallet1:LTC          10 USD

Assets:Crypto:Wallet2:ETH          10 USD

Is there a way to get the "Assets:Crypto:*" sum? Something like:

           account            convert_su

----------------------------- ----------

Assets:Crypto                     60 USD

The more I use beancount (and I am barely scratching the surface), the more I am amazed with the state of the art of software you created. Congratulations indeed! Awesome contribution.

Thank you very much.

[Martin Blais]

On Mon, Nov 20, 2017 at 8:05 PM, Vonpupp <von...@gmail.com> wrote:

Thank you Martin for the nice ideas.

I have some related questions (underlined to try to ease your readability).

Apologies for the delay, I haven't been doing much in the way of new development for a while.

Catching up (see below).

 

First: Here is an output example of the prices:

2017-11-20 price ETH                    367.93000000000000682121026329696178436279296875 USD

2017-11-19 price EUR                              1.173155 USD

2017-11-20 price GAS                    23.3599999999999994315658113919198513031005859375 USD

2017-11-20 price GNT                    0.2210999999999999909849890400437288917601108551025390625 USD

2017-11-20 price LTC                    72.9899999999999948840923025272786617279052734375 USD

On my price source provider I have something like this at the end:

...
price = D(result[symbol][base_currency])
...
return source.SourcePrice(price, time, base_currency)

I wonder, is there a way to limit the decimal places? I tried with the Python Decimal's get_context (if I remember correctly).

This has been annoying me too for a while (but like a lot of things which are imperfect, I've been living with it).

This is supposed to render using the precision in the "display context", which is essentially a set of inferred per-currency statistics on the precision of numbers seen in the input Beancount file. This is automatically compiled by Beancount while reading input files. The idea is to infer the best precision to use to render every currency without the user having to provide these explicitly. The display context compiles two precisions for every currency: the precision of numbers used for "units" (just regular numbers of commodities) which is inferred as the most commonly seen precision in the file, and the precision of numbers used for price, which is inferred as the maximum number of digits seen in the file (for that currency).

I just noticed that there is a bug: the price directives are rendered using the generic directives printer here:

https://bitbucket.org/blais/beancount/src/6292d31f5d14b4bcb282847241b82d9505589e0e/beancount/parser/printer.py?at=default&fileviewer=file-view-default#printer.py-348

That function does accept a display context argument but it's optional, and I'm not using it there:

https://bitbucket.org/blais/beancount/src/6292d31f5d14b4bcb282847241b82d9505589e0e/beancount/prices/price.py?at=default&fileviewer=file-view-default#price.py-334

I've made a change to use the display context argument in rendering those directives:

https://bitbucket.org/blais/beancount/commits/2cf89859aebb30a7df529aba58a139d75450cf55?at=default

Note that this may not entirely solve the issue.... if you have at least one number in your input file which has a very large number of digits, this would set the number of digits to render prices.

The system was built so that the explanation above never would have to be made, but clearly I've failed at that mission, since I've had to explain this many times. I'll admit that when I designed this I erred too far in the direction of making this fully automatic (otherwise I'd have to have explained that for every currency used a special option would have had to be set; anyhow). I'd like to eventually add an option for the user to simply override their favorite / desired precision and completely ignore the inferred one for those currencies.

 

Second: In general the prices source I made seems to work, so I would like to refactor it to a python module, so it is more maintainable and easy to install for others to use, if needed. The big issue is how do I use a python package as a price source provider? 

Currently I have a file on the same folder of the beancount files called cryptocompareusd.py and it works fine (I added to the PYTHONPATH env var), however I have created a package using cookiecutter and the structure is as follows:

.

├── beancount_cryptocompare

│   ├── cryptocompareusd.py

│   └── __pycache__

│       ├── cryptocompareusd.cpython-36.pyc

│       └── __init__.cpython-36.pyc

├── beancount_cryptocompare.egg-info

│   ├── dependency_links.txt

│   ├── not-zip-safe

│   ├── PKG-INFO

│   ├── requires.txt

│   ├── SOURCES.txt

│   └── top_level.txt

├── ...

├── setup.cfg

├── setup.py

I am not sure how to use the provider once on a python package. If I install the package described with `pip install --editable .` and I `import beancount_cryptocompare.cryptocompareusd` it works, however when I use the namespace on the price attribute it doesn't work, example:

2017-11-19 commodity BTC

  export: "Crypto:BTC"

  name: "Bitcoin"

  price: "USD:beancount_cryptocompare.cryptocompareusd/BTC"

This ought to work.

Are you creating a "Source" class in your module, like this?

https://bitbucket.org/blais/beancount/src/6292d31f5d14b4bcb282847241b82d9505589e0e/beancount/prices/sources/oanda.py?at=default&fileviewer=file-view-default#oanda.py-79

The bean-price tool instantiates it here:

https://bitbucket.org/blais/beancount/src/6292d31f5d14b4bcb282847241b82d9505589e0e/beancount/prices/price.py?at=default&fileviewer=file-view-default#price.py-131

 

Third: Is there any way to parse within the `get_latest_price` of the source.Source the external currency? I mean the first part before the ":", in the above example USD. (same as when using the command: `bean-price -e USD:cryptocompareusd/ETH`).

I am using a pip egg that supports querying the price of a cryptocurrency in another currency (USD, EUR, BTC, etc), so what I first thought is to define multiple "price:" attributes for each currency, however I don't know if that makes sense. I haven't yet tried that. What would be the right way to do this?

Yes, you can do that.

Beancount does not attempt to resolve indirect prices (by choice, as that would open a huge can of worms), so you can have any sets of pairs.

One thing it does, however, which could potentially affect this, is enter the "price" of the currencies in both directions in its database.

So for example, if you did this:

  YYYY-MM-DD price  USD   1.20 CAD

  YYYY-MM-EE price   CAD   0.81 USD

both the price database for USD/CAD would have two entries.

Fourth: Related to the last issue, I have found a query to get the current portfolio values using the prices:

bean-query portfolio.beancount "\

select account, convert(sum(position), 'USD') \

group by account \

order by account \

"

This works awesome as expected (thank you!):

           account            convert_su

----------------------------- ----------

Assets:Crypto:Exchange1:BTS        10 USD

Assets:Crypto:Exchange1:CVC        10 USD

Assets:Crypto:Exchange1:STRAT      10 USD

Assets:Crypto:Wallet1:ETH          10 USD

Assets:Crypto:Wallet1:LTC          10 USD

Assets:Crypto:Wallet2:ETH          10 USD

Is there a way to get the "Assets:Crypto:*" sum? Something like:

           account            convert_su

----------------------------- ----------

Assets:Crypto                     60 USD

You should be able to group by some subset of the parent account.

I think something like this:

  select parent(parent(account)), ...

  group by 1

  order by 1

There's always a need for more account name manipulation functions; if you need something that's not there:

https://bitbucket.org/blais/beancount/src/6292d31f5d14b4bcb282847241b82d9505589e0e/beancount/query/query_env.py?at=default&fileviewer=file-view-default#query_env.py-640

Let me know, those are easy to create and add.

 

The more I use beancount (and I am barely scratching the surface), the more I am amazed with the state of the art of software you created. Congratulations indeed! Awesome contribution.

Thank you very much.

Thanks for the nice words!

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/12b6bfdc-73aa-4ed8-a321-b18f5e6b2112%40googlegroups.com.

[Justus Pendleton]

On Tuesday, November 21, 2017 at 8:05:57 AM UTC+7, Vonpupp wrote:

I wonder, is there a way to limit the decimal places? I tried with the Python Decimal's get_context (if I remember correctly).

In the Source I wrote for openexchange (currencies), I limit the decimal places explicitly. The precision is artificially caused by conversion from float to Decimal (so something like 1.2345 turns into 1.234500000000000000000000000001) so I was fine just brute force removing it.

The way you do it with Decimal is the quantize method, not get_context. so something like:

price = D(response['rates'][to_currency]).quantize(D('1.000000'))

[Aamer Abbas]

Vonpupp

I'm looking for a price fetcher for coinmarketcap. Would you by any chance be willing to share your code? Would save me the hassle of having to write one from scratch :-)

[Vonpupp]

Sure @Aamer, it is on a github repo [1].

I got it working when I was developing this but since then a lot of things happened and now I don't remember how to setup the paths for it to work, so you are on your own in this. If you manage to improve the README or anything else for that matter I would be glad to receive PRs =)

I hope it helps a little.

[1]: https://github.com/vonpupp/beancount_cryptocompare

[Aamer]

Somehow just saw this message. Thanks for sharing!

[Aamer]

Just hacked up my own price fetcher. Will attach it here in case anyone finds it useful.

Their API no longer gives historical prices for free, so this relies on scraping (credit to this post: https://medium.com/@danielcimring/downloading-historical-data-from-coinmarketcap-41a2b0111baf).

In order to use:

1.) Create folder "price_sources" in your beancount folder

2.) Create an empty __init__.py file in this folder
3.) Place my attached file price_sources/coinmarketcap.py

4.) Add this as the source for your crypto commodity. For example, for bitcoin, you'd add: "price: "USD:price_sources.coinmarketcap/bitcoin" as the attribute. Note "bitcoin" corresponds to the coinmarketcap url (in this case, the url is https://coinmarketcap.com/currencies/bitcoin/historical-data/)

5.) ???

6.) Profit?!

By the way, I am not a Python programmer, so this code probably sucks.

Also, fair warning that Coinmarketcap blocks you if you send too many requests; for this reason, I've cnofigured it to wait 1 second before sending a request. Be a good internet citizen and don't abuse their service, please!

[Aamer]

Oh, forgot to add ... you should also add "." (the current folder) to $PYTHONPATH

[Martin Blais]

Hi Aamer,

Thanks for sharing your code.

Perhaps the best way to share it would be to create a github repository.

People can install code from there and keep track of updates you push as it evolves.

It doesn't have to live in your beancount folder BTW, the path you specify merely has to be on your PYTHONPATH.

Cheers,

--

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 post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/752a8ea4-cfde-4e0b-a161-99696cda9f79%40googlegroups.com.

[Aamer Abbas]

Sure thing. Done: https://github.com/aamerabbas/beancount-coinmarketcap

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPYvXDE-k6Ubo2dgOZqnZDTVA-4XjaV_-0T0SSCJ46K-g%40mail.gmail.com.

[Kirill Goncharov]

Hi,

I want to share some tools that I created.

Beancount cryptoassets: https://github.com/xuhcc/beancount-cryptoassets

This package contains price sources for cryptocurrencies based on Coinmarketcap API and Cryptonator API, but also price sources for some DeFi assets (Compound, bZx, TokenSets).

Ethereum transaction importer for Beancount: https://github.com/xuhcc/beancount-ethereum-importer

It includes a script that downloads transactions from Etherscan and an importer for downloaded transactions.

Price source for http://exchangeratesapi.io/ (not related to crypto, but I haven't shared it before): https://github.com/xuhcc/beancount-exchangerates

[Martin Blais]

Thanks for sharing these Kirill, added to the contrib document.

--

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/91101592-cfd2-4e70-8622-8357474f31a5%40googlegroups.com.