I know this won't solve your issue. What I've done in the past was to read the data to a Pandas DataFrame, and aggregate and query it using DuckDB
On Sunday, March 17, 2024 at 10:12:30 PM UTC-3 and...@gerstmayr.me wrote: > > Hi! > > > > I'm trying to get a report of the market value of my assets at the end > > of each month: > > > > SELECT year, month, > > CONVERT(LAST(balance), 'USD', DATE_ADD(DATE(year, month+1, > > 1), -1)) AS market_value, > > CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(year, month+1, > > 1), -1)) AS book_value > > WHERE account ~ '^Assets:' > > GROUP BY year, month > > It does work with the new beanquery when using FIRST(): > > SELECT year, month, > CONVERT(LAST(balance), 'USD', DATE_ADD(DATE(FIRST(year), > FIRST(month)+1, 1), -1)) AS market_value, > CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(FIRST(year), > FIRST(month)+1, 1), -1)) AS book_value > WHERE account ~ '^Assets:' > GROUP BY year, month > > However there's an issue in December, as month+1 doesn't work if month > is December :| > > I think a new BQL function is required to support this use case (happy > to provide a PR). > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/5ed11b1c-8d14-4a9b-956a-1c7aa8efb9bdn%40googlegroups.com.
