Miniscule Share Residuals

[John Ralls]

> On Oct 29, 2016, at 11:51 AM, David T. <sunfish62 at yahoo.com> wrote:
> 
>> 
>> On Oct 29, 2016, at 11:36 PM, John Ralls <jralls at ceridwen.us> wrote:
>> 
>> 
>>> 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
>> 
> 
> 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.

David, 

If you're just trying to determine if the account holds shares then the force-to-double-and-truncate will work for most cases. The exception might be a mutual fund with reinvested dividends where floating point math might still leave a residual that's larger than the commodity's smallest fraction.

The second method, where you sum the numerator and then divide by the denominator works if the denominator is the same for all rows; if it isn't you'll get the wrong answer.

Regards,
John Ralls