SQL Portfolio Report (was Re: Monthly Income/Expense Reports)

[David G. Hamblen]

Attached are two files for generating a Portfolio report/graph from 
Gnucash using SQL queries to create a table called portfolio within the 
GC database which can be opened in a spreadsheet (OOCalc).  As always, 
have backups in case something goes wrong, and remember that using an 
external program to access the GC data is officially frowned upon!

The first file is a script which calls the second file, which creates 
one huge SQL command.  The script has the dates and date increment for 
the data, and within the SQL command are references to level2, which is 
specific to my Chart of Accounts and lets me separate tax-deferred 
investments (IRA) from taxable ones.   The created table has seven 
columns (ValueDate, mnemonic, level, shares, price, cost, value).

It takes about 15 seconds to generate the table using my data and once 
the OOCalc pivot table and graph are set up and saved, refreshing the 
plotted data is quick (~2 seconds).  This compares to 100 sec for the 
standard "Asset Barchart" for the same time frame.

BTW, the original posting for the portfolio is: 
https://lists.gnucash.org/pipermail/gnucash-user/2010-September/036424.html 
.  That version uses the most recent value.  I modified it to use the 
value on the date nearest to a given date, so that I could loop and 
generate a plot vs time.

Dave


On 04/01/2011 07:24 PM, Martin Cunningham wrote:
> Please do when you get a chance, David - i would be very interested. I 
> found the thread you mentioned here, incidentally:
> https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html
> Thank you. Great to know there are like-minded users out there.
>
> Martin Cunningham
> T: (415) 448-6854
>
>
> On Fri, Apr 1, 2011 at 3:34 PM, David G. Hamblen 
> <dhamblen at roadrunner.com <mailto:dhamblen at roadrunner.com>> wrote:
>
>     I've ported the SQL code posted by markluser back on Jul 20 2009
>     (you should be able to find the thread in the gnucash-user
>     archives).  His
>

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: PortfolioScript
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110405/18f95d67/attachment.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: Portfolio-sql
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110405/18f95d67/attachment.cc>