> On Oct 29, 2016, at 11:36 PM, John Ralls <[email protected]> wrote: > > >> On Oct 29, 2016, at 11:28 AM, David T. <[email protected]> wrote: >> >> Not sure if this is right, but the following seems to work: >> >> SELECT c.mnemonic as SYMBOL, >> ROUND(SUM((s.quantity_num*1.0/s.quantity_denom)), LENGTH(REPLACE(c.fraction, >> '1', ''))) as SHARES /* rounding to the number of precision indicated in >> Commodities */ >> FROM accounts as a, commodities as c, splits as s >> WHERE (a.account_type='MUTUAL' OR a.account_type='STOCK') >> AND a.guid=s.account_guid >> AND a.commodity_guid=c.guid >> GROUP BY c.mnemonic >> ORDER BY SHARES; >> >> Is that what you meant? > > David, > > No, but it's probably close enough for your needs. > > It would be a challenging set of queries to the right way. The first step > would be something like sum(s.quantity_num) group by s.quantity_denom, but > then finding the LCD with a SQL query is beyond my fluency in SQL. I'd be > inclined to take that result and pass it to a Python or Perl script to do the > rest. > > Or you could just run Reports>Assets>Investment Portfolio and set "Show > Hidden Accounts" and "Include Accounts with No Shares" in Options>Accounts. > > Regards, > John Ralls >
John, This is the first part of a process to create a price database with monthly prices for all holdings in a file. My first goal is to identify the commodities in the file, the holdings, and the earliest and latest splits on file for each. Then, I plan to use the earliest date as a starting point for the monthly retrieval of prices using Finance::QuoteHist. The process would continue until the latest date for zero-share holdings, and until today for non-zero holdings. Then, the results would be inserted into the prices table. Thus, my interest in calculating total shares. And yes, this is close enough for my needs; for yucks, I also tried summing the quantity_num, then dividing by quantity_denom and rounding, with the same result. Cheers, David _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
