> 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 > >> On Oct 29, 2016, at 11:13 PM, David T. via gnucash-devel >> <[email protected]> wrote: >> >> >>> On Oct 29, 2016, at 9:44 PM, John Ralls <[email protected]> wrote: >>> >>> >>>> On Oct 29, 2016, at 9:16 AM, David T. <[email protected]> wrote: >>>> >>>> John, >>>> >>>> Probably. As in, probably I did something wrong. (What else is new? ) >>>> >>>> When I first queried the database, it returned only integers when I >>>> calculated the shares, so I Googled to find out how to get some decimals. >>>> My solution was to multiply by 1.0, which yielded the aforementioned >>>> results. >>>> >>>> Specifically, my query included SUM (shares*1.0/shares_denom). This >>>> resulted in the residuals. >>>> >>>> So, what is the "rational" way to calculate the shares? >>>> >>> >>> David, >>> >>> Rational math is what you learned in primary school: Find the least common >>> denominator, convert every fraction to be expressed with that denominator, >>> then add the numerators and simplify the sum. Always use integers, no >>> decimal points. >>> >>> The reason that's necessary is that 1/10 isn't exactly representable in >>> binary, so for the computer 0.1 + 0.9 - 1.0 != 0.0 at infinite precision. >>> The error accumulates and is larger when more decimal places are involved >>> so for more complex calculations the error can become large enough to >>> display, as you discovered. >>> >>> Regards, >>> John Ralls >> >> OK, but my question remains. In SQLite3, if I use the following: >> >> SELECT c.mnemonic as SYMBOL, >> SUM((s.quantity_num/s.quantity_denom)) as SHARES /* Note the splits fields >> used without any treatment */ >> 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; >> >> I get ONLY integral results for SHARES. A search online tells me that if I >> use: >> >> SELECT c.mnemonic as SYMBOL, >> SUM((s.quantity_num*1.0/s.quantity_denom)) as SHARES /* Note the splits >> fields used with treatment to force float */ >> 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; >> >> Then, I get decimals, but also these residuals. So, please tell me what >> exactly I should do to get an accurate accounting of the shares in the file? >> >> TIA, >> David >> _______________________________________________ >> gnucash-devel mailing list >> [email protected] >> https://lists.gnucash.org/mailman/listinfo/gnucash-devel > _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
