Miniscule Share Residuals

[John Ralls]

> On Oct 29, 2016, at 11:28 AM, David T. <sunfish62 at yahoo.com> 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 <gnucash-devel at gnucash.org> wrote:
>> 
>> 
>>> On Oct 29, 2016, at 9:44 PM, John Ralls <jralls at ceridwen.us> wrote:
>>> 
>>> 
>>>> On Oct 29, 2016, at 9:16 AM, David T. <sunfish62 at yahoo.com> 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
>> gnucash-devel at gnucash.org
>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>