Miniscule Share Residuals
David T.
sunfish62 at yahoo.com
Sat Oct 29 14:13:03 EDT 2016
> 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
More information about the gnucash-devel
mailing list