Looks like you need to ask for a new function to be added to the beanquery, something like MONTH_END_DATE(year, month). In addition also QUATER_END_DATE (year, month) would be useful.
Looking at the code of the similar functions <https://github.com/beancount/beanquery/blob/6df80214d1c1f8aabdba0af732e5e1a36f631e43/beanquery/query_env.py#L127>, that shall be quite easy. I personally solve this in the following way: 1) Generate the dates I am interested in in python (e.g. every week/month/quarter/year end) 2) Generate the market value for these dates using the following query: query = f""" SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount where date <= {date_iso} AND account ~ 'Assets|Liabilities' """ 3) But I use beancount from jupyter notebook, not from command line On Wednesday, May 29, 2024 at 4:10:54 AM UTC+2 francocalvo wrote: > 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/dd162eb1-5e63-47b5-90ed-9b12ff4abe96n%40googlegroups.com.
