Couple of questions on BeanQueryLanguage

[Peter]

Hi,

I'm trying to figure out how to use BQL properly. I ran into some cases where I don't know what I am doing wrong:

Properly use the WHERE clause

==========================

I tried to select all lots which market value is higher than the book value/lot cost.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date as date, cost(sum(position)) as book_value, value(sum(position)) as market_value WHERE market_value > book_value

Error:

ERROR: Invalid column name 'market_value' in WHERE clause context.

Why is market_value (and book_value) an invalid column name? How can I use it in a WHERE clause?

Selecting all lots older than 365 days:

==============================

I tried to select all lots which are older than 365 days (using the lot date). I can't just use date as it uses the transaction date. As I transferred lots between accounts the transaction date differs from the lot date.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date WHERE date_diff(today(), cost_date) > 365

Exception:

Traceback (most recent call last):

  File "/usr/lib/python3.8/cmd.py", line 214, in onecmd

    func = getattr(self, 'do_' + cmd)

AttributeError: 'BQLShell' object has no attribute 'do_SELECT'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 271, in run_parser

    self.dispatch(statement)

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 251, in dispatch

    return method(statement)

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 416, in on_Select

    rtypes, rrows = query_execute.execute_query(c_query,

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/query_execute.py", line 317, in execute_query

    if c_where is None or c_where(context):

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/query_compile.py", line 121, in __call__

    return self.operator(self.left(context), self.right(context))

TypeError: '>' not supported between instances of 'NoneType' and 'int'

Calculate in a query

================

Is it possible to calculate in a query? I'd like to calculate unrealized P/L in a query, if that's possible:.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date as date, cost(sum(position)) as book_value, value(sum(position)) as market_value, value(sum(position)) - cost(sum(position)) as profit_loss value(sum(position)) / cost(sum(position)) as profit_loss_perc

Separate the number/unit and currency of prices/lots, etc

===========================================

How can I separate/split the number/unit and the currency in two columns?

I'd like an output like:

account | lot_units (Number) | lot_ccy (Currency Symbol) | book_value (Number) | market_value (Number) | value_ccy (Currency Symbol)

What I got so far:

account | lot_units (Number and Currency) | lot_ccy (Currency Symbol) | book_value (Number and Currency) | market_value (Number and Currency)

Query:

SELECT account, units(sum(position)) as lot_units, currency as lot_ccy, cost(sum(position)) as book_value, value(sum(position)) as market_value, cost_currency as value_ccy GROUP BY account, lot_ccy, cost_date, value_ccy ORDER BY account, currency, cost_date

If I try to use NUMBER() on any of the targets I get an error.

> Error: ERROR: Invalid type for argument 0 of Number: found expected .

Any help is appreciated.

Regards

[Martin Blais]

On Sun, Dec 27, 2020 at 8:55 AM Peter <tobias....@gmail.com> wrote:

Hi,

I'm trying to figure out how to use BQL properly. I ran into some cases where I don't know what I am doing wrong:

Properly use the WHERE clause

==========================

I tried to select all lots which market value is higher than the book value/lot cost.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date as date, cost(sum(position)) as book_value, value(sum(position)) as market_value WHERE market_value > book_value

Error:

ERROR: Invalid column name 'market_value' in WHERE clause context.

Why is market_value (and book_value) an invalid column name? How can I use it in a WHERE clause?

It's not very sophisticated, aliases in the where clause aren't working yet.

  

Selecting all lots older than 365 days:

==============================

I tried to select all lots which are older than 365 days (using the lot date). I can't just use date as it uses the transaction date. As I transferred lots between accounts the transaction date differs from the lot date.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date WHERE date_diff(today(), cost_date) > 365

DATE_DIFF() is not super polished yet. Send me a patch.

This whole bean-query tool was always a prototype. I'm planning to write it properly after v3's core is rewritten.

Not enough cycles

 

Exception:

Traceback (most recent call last):

  File "/usr/lib/python3.8/cmd.py", line 214, in onecmd

    func = getattr(self, 'do_' + cmd)

AttributeError: 'BQLShell' object has no attribute 'do_SELECT'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 271, in run_parser

    self.dispatch(statement)

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 251, in dispatch

    return method(statement)

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/shell.py", line 416, in on_Select

    rtypes, rrows = query_execute.execute_query(c_query,

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/query_execute.py", line 317, in execute_query

    if c_where is None or c_where(context):

  File "/usr/local/lib/python3.8/dist-packages/beancount/query/query_compile.py", line 121, in __call__

    return self.operator(self.left(context), self.right(context))

TypeError: '>' not supported between instances of 'NoneType' and 'int'

Calculate in a query

================

Is it possible to calculate in a query? I'd like to calculate unrealized P/L in a query, if that's possible:.

Query:

SELECT account, units(sum(position)) as units, cost_number as cost, cost_date as date, cost(sum(position)) as book_value, value(sum(position)) as market_value, value(sum(position)) - cost(sum(position)) as profit_loss value(sum(position)) / cost(sum(position)) as profit_loss_perc

VALUE is defined to return types Position and Inventory

Subtraction and division of those isn't going to be meaningful, needs to be reduced further.

 

Separate the number/unit and currency of prices/lots, etc

===========================================

How can I separate/split the number/unit and the currency in two columns?

bergamot:~/p/beanlabs$ bean-query --help

...

  -m, --numberify       Numberify the output, removing the currencies.

 

I'd like an output like:

account | lot_units (Number) | lot_ccy (Currency Symbol) | book_value (Number) | market_value (Number) | value_ccy (Currency Symbol)

What I got so far:

account | lot_units (Number and Currency) | lot_ccy (Currency Symbol) | book_value (Number and Currency) | market_value (Number and Currency)

Query:

SELECT account, units(sum(position)) as lot_units, currency as lot_ccy, cost(sum(position)) as book_value, value(sum(position)) as market_value, cost_currency as value_ccy GROUP BY account, lot_ccy, cost_date, value_ccy ORDER BY account, currency, cost_date

If I try to use NUMBER() on any of the targets I get an error.

> Error: ERROR: Invalid type for argument 0 of Number: found expected .

Any help is appreciated

bean-query was always and is still a bit of a prototype which has served its purpose to prove that this can be the way forward.

We'll need to rewrite this properly, and that's best done outside the narrow scope of beancount

see http://furius.ca/beancount/doc/v3

In any case, for what you need to do, you should write custom scripts.