GnuCash recreating Quicken Rental Report

Question

I am a quicken user hoping to transfer to GnuCash. I like GnuCash a lot, but am having trouble with creating some specific reports that (IMO) are easy to create in Quicken.

Specifically, I have several rental houses, and in Quicken I have an account for each rental house, as I will (I assume) in GnuCash. The corresponding "rental expense" and "rental income" categories and subcategories I have in Quicken will become accounts (and subcategories child accounts) in GnuCash. Come tax time, I like to produce a list of income and expenses for the preceding year for each rental property account. I don't need (or want, typically) to see individual transactions. Rather for each rental property I want a report (or in a perfect world, a single multicolumn report with each column representing a rental property) that lists all the income category (accounts) totals, and ideally indented beneath them the subcategory (sub account) totals for the year.

Thus for Property A, I would have a report including a line with total expenditures for utilities (say) for that year on property A, and ideally indented beneath that, subtotals for the year for Gas, Water, Electricity, etc. Similarly a line with total income for that property for that year (ideally with indented lines of subcategories/subaccounts like total rents, total late fees, etc).

I can create a crude version of this in GnuCash using the income/expense reports, but I don't see how to limit it to just those transactions that occurred for Property A, rather than aggregating ALL my rental properties.

I am familiar with MySQL, and have toyed with digging into the DB schema to roll my own reports/queries. But that seems like a pretty big time commitment for something that is easily done in Quicken.

I'd really like to ditch Quicken, but am hesitant given either my lack of understanding of GnuCash reports, or the lack of capability of GnuCash reports. Any suggestions/tips or pointing me to the appropriate resources to learn?

Here's an example (with some categories/accounts expanded - e.g. Labor - to show each transaction, and others collapsed - e.g. Rents Received - to show just totals.

And a perhaps not-too-useful snapshot of my (say) Property A transactions:

Answer 1

Gnucash is first and foremost just a general ledger system. It tracks money in accounts, and lets you make transactions to transfer money between the accounts, but it has no inherent concept of things like taxes. This gives you a large amount of flexibility to organize your account hierarchy the way you want, but also means that it sometimes can take a while to figure out what account hierarchy you want. The idea is that you keep track of where you get money from (the Income accounts), what you have as a result (the Asset accounts), and then track what you spent the money on (the Expense accounts).

It sounds like you primarily think of expenses as each being for a particular property, so I think you want to use that as the basis of your hierarchy. You probably want something like this (obviously I'm making up the specifics):

• Assets
  • Checking
  • Savings
• Income
  • Bank Interest
  • Property A
    • Rent Received
    • Money found in couch cushions of property A
  • Property B
    • Rent Received
    • Money found in couch cushions of property B
• Expenses
  • Property A
    • Insurance
    • Labor
    • Repairs
    • Taxes
    • Utilities
      • Electric
      • Gas
  • Property B
    • Insurance
    • Labor
    • Repairs
    • Taxes
    • Utilities
      • Electric
      • Gas

Now, when running transaction reports or income/expense reports, you can filter to the accounts (and subaccounts) of each property to get a report specific to that property.

You mention that you also sometimes want to run a report on "all gas expenses, regardless of property", and that's a bit more annoying to do. You can run the report, and when selecting accounts you have to select all the Gas accounts individually. It sounds like you're really looking for a way to have each transaction classified in some kind of two-axis system, but the way a general ledger works is that it's just a tree, so you need to pick just one "primary" axis to organize your accounts by.

Answer 2

Here's a very basic MySQL query I put together that does what I want for income/expense report. Basically it reports the same info as the canned income/expense report, but limits it those income/expenses associated with a particular account (rental property, in my case). My main complaint is the output "report" is pretty ugly. And modifying for a different rental property requires changing the code (I could pass parameters etc).

Again, the main "issue" in my mind with GnuCash income/expense report is that there is no filter for which account (rental property) you want income/expenses for, unless you set up account tree so that each rental property has its own defined incomes and expenses (i.e. PropertyA:Expense:Utility:electric).

Hopefully someone will point me to a more elegant solution that uses the report generator built into GnuCash. THanks!

SELECT a2.account_type , a4.name, a3.name, a2.name, SUM(ROUND(IF(a2.account_type='EXPENSE',- s2.value_num,ABS(s2.value_num))/s2.value_denom,2)) AS amt FROM ( SELECT s1.tx_guid FROM gnucash.accounts AS a1 INNER JOIN gnucash.splits AS s1 ON s1.account_guid = a1.guid WHERE a1.name='Property A' ) AS X INNER JOIN gnucash.splits s2 ON x.tx_guid = s2.tx_guid INNER JOIN gnucash.accounts a2 ON a2.guid=s2.account_guid INNER JOIN gnucash.transactions t ON t.guid=s2.tx_guid LEFT JOIN gnucash.accounts a3 ON a3.guid = a2.parent_guid LEFT JOIN gnucash.accounts a4 ON a4.guid = a3.parent_guid WHERE a2.name <> 'Property A' # get all the accounts associated with tx in Property A account (but not the actual Property A Bank duplicate entries. AND t.post_date BETWEEN CAST('2016-01-01' AS DATE) AND CAST('2016-12-31' AS DATE) GROUP BY a2.account_type ,a4.name, a3.name, a2.name WITH ROLLUP ;

And here's the output. Hopefully someone has a better suggested approach!

Answer 3

I don't see how to limit it to just those transactions that occurred for Property A, rather than aggregating ALL my rental properties.

Use a filter query. Reports can be filtered by characters (tags) on split memo, transaction notes, or transaction description. Note that this will only report transactions/splits that match the filter.

https://github.com/dawansv/gnucash-custom-reports is a custom transaction report that will optionally report all transactions by filter. This means you can clearly see the transactions which aren't part of the filter, often because you didn't add the tags to the split/transaction.

I want [a rental report]

https://wiki.gnucash.org/wiki/Using_GnuCash#Printing_a_Rental_Report