On Sun, Dec 27, 2020 at 8:55 AM Peter <[email protected]> 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. -- 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/CAK21%2BhOuC9ZPbi9Ybw3JWZJrWxEsC9rDdZbSmvO%3Dk319oD77Rw%40mail.gmail.com.
