Miniscule Share Residuals

[David T.]

> 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.

Cheers,
David