Trading: 1) How To Report Trading Lots & 2) Trade Analysis

[David E]

Hello, I am very new to Beancount as I want to keep track of my trading activities and generate reports with FIFO settings. I love the tool so far and the community around it.

1)
To get started, I want to better understand how Lots are handled and therefore want to report active Lots. In the docs (how inventories work > cost basis) there is a very good example of what I am looking for 

```units ccy cost cost-ccy lot-date label
 25 HOOL {23.00 USD, 2015-04-01, "first-lot"} 35 HOOL {27.00 USD, 2015-05-01, None}```

I tried `bean-report books.beancount holdings` but only get a total of the account, not the active Lots within. what else could I try?

2) 
As a bonus, I would like to ask how it could be possible to track my trades so that I can analyse the outcome. I am thinking of categories like timeframe or setup. The query for example could be: show me the outcome of all trades based on the 1H chart (this way I could see where my profits come from, interesting to know). problem I see is that I would have to book it in order to have it available to query, but I would make an entry for PnL instead, right? 

looking forward to what you have in mind about it.
thanks

[Martin Blais]

On Mon, Mar 8, 2021 at 9:32 AM 'David E' via Beancount <bean...@googlegroups.com> wrote:

Hello, I am very new to Beancount as I want to keep track of my trading activities and generate reports with FIFO settings. I love the tool so far and the community around it.

1)
To get started, I want to better understand how Lots are handled and therefore want to report active Lots. In the docs (how inventories work > cost basis) there is a very good example of what I am looking for 

```units ccy cost cost-ccy lot-date label
 25 HOOL {23.00 USD, 2015-04-01, "first-lot"} 35 HOOL {27.00 USD, 2015-05-01, None}```

I tried `bean-report books.beancount holdings` but only get a total of the account, not the active Lots within. what else could I try?

This this script, which runs the booking code by hand over your transactions and stores the matches ("trades"):

https://github.com/beancount/beanlabs/blob/master/beanlabs/trades/match_trades.py

In v3, it will be built-in and the result of booking will leave traces in the postings in order to rebuild the trades without having to rebook everything.

See "Trade Matching and Reporting" section of this doc:

https://docs.google.com/document/d/1qPdNXaz5zuDQ8M9uoZFyyFis7hA0G55BEfhWhrVBsfc/edit#heading=h.1on91pqrb473

In the meantime the script I shared should do the job, and will work on current v2 branch.

 

2) 
As a bonus, I would like to ask how it could be possible to track my trades so that I can analyse the outcome. I am thinking of categories like timeframe or setup. The query for example could be: show me the outcome of all trades based on the 1H chart (this way I could see where my profits come from, interesting to know). problem I see is that I would have to book it in order to have it available to query, but I would make an entry for PnL instead, right? 

You should build your own using the results of the above. 

I've done a bunch of custom scripts and many of these aren't shared - not polished enough - although last year I took some time to structure my returns calculation based on cash flows and shared it here:

doc: http://furius.ca/beancount/doc/returns 

repo: https://github.com/beancount/beangrow

For options and futures, I have a separate set of scripts, but those aren't neat enough to share yet, as they remain with some amount of dependence on my particular style of trading, strategies and they're made more complicated by options on futures and pairs trades. I'm in a position to have to generalize it across multiple brokers now so I may end up putting that out there eventually.

N.B. If you're on TD, this will come in handy:

https://github.com/blais/ameritrade/blob/master/examples/ameritrade2beancount.py

I also share an IBKR importer:

https://github.com/beancount/beanlabs/blob/master/beanlabs/importers/ibkr/ibkr_flex_reports_csv.py

and have a pretty basic Tastyworks one I could share if anyone cares.

Hope this helps,

looking forward to what you have in mind about it.
thanks

--
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/b028cb55-2900-40e7-a473-c1330c5ce2dbn%40googlegroups.com.

[david e]

thanks a lot for taking time to answer. I am very new to beancount and I am not a programmer, so this will take a little longer to implement. 

I will try to get started by running scripts like the one you provided, and hope to be able to can access it via CLI (like having the script available similar to a `bean-report` action. gonna read the docs and start step by step. 

it is my goal to have an overview of my current positions and from what I have seen so far, beancount is just perfect for that purpose. even without programming skills the advantage of having the tool to report my action exactly the way I want is promising and worth to get into this field.

I am not trading futures or options, but sometimes using leverage. 

but my first task is to get my current positions into my main file (some are from 2018/2019 and I have to figure out how to handle the cost basis), some are cryptocurrencies and others are stocks. 

I do have a lot more of trading related questions, do you prefer to have them available as separate conversations or should I put them into here?

thanks 

[Martin Blais]

On Tue, Mar 9, 2021 at 1:24 PM david e <in...@davidebner.de> wrote:

thanks a lot for taking time to answer. I am very new to beancount and I am not a programmer, so this will take a little longer to implement. 

I will try to get started by running scripts like the one you provided, and hope to be able to can access it via CLI (like having the script available similar to a `bean-report` action. gonna read the docs and start step by step. 

it is my goal to have an overview of my current positions and from what I have seen so far, beancount is just perfect for that purpose. even without programming skills the advantage of having the tool to report my action exactly the way I want is promising and worth to get into this field.

Oh, to compute your positions, you can use the export script.

There's an old document but it's vastly out-of-date, bean-report is deprecated, and Google Finance has been shutdown:

https://docs.google.com/document/d/1mNyE_ONuyEkF_I2l6V_AoAU5HJgI654AOBhHsnNPPqw/edit#heading=h.9lk1l7gqxxfs

However, the script is still very much alive; I'm using it regularly.

It converts your Ledger to a CSV file with one line per lot/asset, and then I provide another script which uploads that to a Google Sheets doc.

You can then compute various aggregations from that, e.g. Day P/L after tax (you do it yourself, in the spreadsheet).

I have a Makefile with these rules to generate and upload the sheets doc:

assets.csv: ledger.beancount
        python3 -m beancount.projects.export ledger.beancount -o $@
export-upload: assets.csv
        upload-to-sheets --min-rows=300 -v --docid="DOCID" Data:$<

where you replace the DOCID by the document's id (the long garbled part of the URL).

I am not trading futures or options, but sometimes using leverage. 

Should be simpler then.

 

but my first task is to get my current positions into my main file (some are from 2018/2019 and I have to figure out how to handle the cost basis), some are cryptocurrencies and others are stocks. 

I do have a lot more of trading related questions, do you prefer to have them available as separate conversations or should I put them into here?

Separate threads if you can.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/71ecf0a8-a4c3-4d97-94e0-346bc4b3afbfn%40googlegroups.com.

[david e]

thank you, I am not quite sure how to implement the script you provided above in your first code (the beanlabs/match_trades.py). do I put it into my main file or where does it sit and how to invoke it?

but what I found in the meantime is this script that also allows me to check the inventory of a stock:

```SELECT account, units(sum(position)) as units, cost_number as cost, first(getprice(currency, cost_currency)) as price, cost(sum(position)) as book_value, value(sum(position)) as market_value, cost_date as acquisition_date WHERE account_sortkey(account) ~ "^[01]" GROUP BY account, cost_date, currency, cost_currency, cost_number, account_sortkey(account) ORDER BY account_sortkey(account), currency, cost_date```

by adding this script to my main file I should be able to run the command like bean-query file.bean "Script", right?

is there a way to handle the "^[01]" like a variable so that I can pick the stock I want to check with a flag for example 

> bean-query file.bean "Script" -onlyShowSpecificStock?

thanks!

[david e]

update:

in the meantime I just found a way to implement the script "SELECT …" into my file.bean and run it via the query prompt. this works great after I removed the WHERE line.

Now I am wondering if there is a way to run this script but with a specific stock selected only? this works when I run the script directly from CLI by replacing the ^[01] with the stock I want to query. but when running it via query prompt, this isn't possible.

can I run the script with flags/filters in order to pick a specific stock only? this could sound like

bean-query myfile.bean

run myScript -filter "specificStock"

thanks

[Martin Blais]

On Thu, Mar 11, 2021 at 7:39 AM david e <in...@davidebner.de> wrote:

update:

in the meantime I just found a way to implement the script "SELECT …" into my file.bean and run it via the query prompt. this works great after I removed the WHERE line.

Now I am wondering if there is a way to run this script but with a specific stock selected only? this works when I run the script directly from CLI by replacing the ^[01] with the stock I want to query. but when running it via query prompt, this isn't possible.

Yes insert 

WHERE currency = 'AAPL' 

for example

 

can I run the script with flags/filters in order to pick a specific stock only? this could sound like

bean-query myfile.bean

run myScript -filter "specificStock"

thanks

I would recommend doing everything in a script.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/9f766285-680f-452c-b940-9b65fddf035dn%40googlegroups.com.