Fw: Account Balance

[Phil Longstaff]

Please remember to reply-all to forward to the list.

----- Forwarded Message -----
From: Mark Gillis <mark at untangledweb.co.uk>
To: 'Phil Longstaff' <phil.longstaff at yahoo.ca> 
Sent: Wednesday, December 14, 2011 8:21:46 AM
Subject: RE: Account Balance
 

Thanks Phil
 
That was the formula I was after. The problem I was left with was that I needed to see the running total, at the point each transaction took place. So, for instance I have 3 separate transactions:
 
root_name parent_name Transaction_Date Number_of_Shares Amount_invested Share_Price 
Assets Investments 2009-04-01 931.0152 1420.4500 1.52570012 
Assets Investments 2009-09-22 474.5925 990.0000 2.08600009 
Assets Investments 2009-11-02 209.4532 435.6000 2.07970086 
 
But I need the running total as it was at each date:
 
root_name parent_name Transaction_Date Number_of_Shares Amount_invested Share_Price Total_Shares 
Assets Investments 2009-04-01 931.0152 1420.4500 1.52570012 931.0152 
Assets Investments 2009-09-22 474.5925 990.0000 2.08600009 1405.6077 
Assets Investments 2009-11-02 209.4532 435.6000 2.07970086 1615.0609 
 
I found a way of doing this but it’s a bit clunky. If you, or anyone else in the forum, have a neater way, please let me know. Otherwise if anyone wants to use the SQL, feel free
 
Cheers
Mark
 
SELECT
    root_name
    ,parent_name
    ,Date(transactions.post_date)   as Transaction_Date
    ,(a1.quantity_num)/10000        as Number_of_Shares        
    ,(a1.value_num)/100             as Amount_invested
    ,((a1.value_num)/100) / ((a1.quantity_num)/10000)           as Share_Price        
    ,sum( a2.quantity_num ) / a2.quantity_denom as Total_Shares 
FROM    splits  a1     
        inner join
        transactions        on a1.tx_guid=transactions.guid
        inner join
        accounts_hierarchy  on target_guid=a1.account_guid 
        , splits  a2
where   (a1.quantity_num <= a2.quantity_num 
        and a1.account_guid=a2.account_guid)
GROUP BY a1.account_guid , transactions.post_date
ORDER BY transactions.post_date ASC, grandchild_name DESC
 
From:Phil Longstaff [mailto:phil.longstaff at yahoo.ca] 
Sent: 07 December 2011 18:28
To: Mark Gillis; gnucash-user at gnucash.org
Subject: Re: Account Balance
 
Hi Mark,
 
if you use the query:
 
SELECT account_guid, reconcile_state, sum(quantity_num) as quantity_num, quantity_denom FROM %s GROUP BY account_guid, reconcile_state, quantity_denom ORDER BY account_guid, reconcile_state
 
then you will get a select response with rows of:
 
account guid, reconciled state, total (numerator), denominator
 
I think it is true that for all splits within an account, the denominator is the same.  If not, you will need to appropriately combine rows.  Of course, the value is numerator divided by denominator.  If you don't care about reconcile state, just ignore that bit.
 
Phil
 

________________________________

From:Mark Gillis <mark at untangledweb.co.uk>
To: gnucash-user at gnucash.org 
Sent: Wednesday, December 7, 2011 12:37:19 PM
Subject: Account Balance

I'm writing some queries to extract data from the MySQL backend database for
GnuCash. I've got most of what I need but I'm struggling to locate a data
item for the Account Balance. Is this actually stored in the database or is
it calculated from the other data items? If the latter, does anyone have the
formula for the account running total balance? (It's the total number of
shares, shown in the Balance column of a Share Account that I'm after, if
that makes any difference)
Cheers
Mark 

--
View this message in context: http://gnucash.1415818.n4.nabble.com/Account-Balance-tp4169820p4169820.html
Sent from the GnuCash - User mailing list archive at Nabble.com.
_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.