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
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/be10e3e9-a6b2-44e2-a22e-2798473238a1n%40googlegroups.com.
