4

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.

Rental Property A

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

  • Can you clarify your account structure, and maybe sketch out an example of the expected report? You say you have separate expense accounts (with subaccounts) for each property, so I would think you'd just run a report filtered to the account (and subaccounts) for each property. So I think I'm not quite following what you're trying to do and what the difficulty is. – Peter Cooper Jr. Mar 3 '17 at 14:07
  • Thanks for the reply. I don't have separate expense accounts for EACH property - I have one (say) utilities account with subaccounts electric, gas, water. And then I have one account account for each property. Thus in my property A account I'll input a record for electric charges for current month, and do the same thing in my property B account. But I want totals (obviously) reported separately by each account. It may be some simple mechanics I need to do in my report generation that I'm just not seeing (filters? but those are only on transaction reports?). Like I said...I'm a newbie. – pholck Mar 3 '17 at 14:38
  • Is there a reason you don't want separate expense accounts for each property? Since it sounds like that's exactly what you're looking for, if you want to break out your expenses by property. – Peter Cooper Jr. Mar 3 '17 at 14:40
  • I guess not in theory - just seems like a LOT of accounts then and way-confusing. Then when I enter a transaction rather than just choose utility:gas I have to choose Property A:utility:gas. And with a lot of properties that's a pain. ANd of course at times I like reports of all utilities combined (across properties displayed). Just seems like a clunky solution when I should be able to filter WHICH property account I want to display income and expenses for. Example (image/link) now included in original post. THanks! – pholck Mar 3 '17 at 14:47
  • If you're not choosing a property for the expense at the time you enter the transaction, how would there be data to filter on? I feel like we're still not communicating, but it's likely that I'm just not familiar with Quicken and its approach to things. Gnucash is (kinda) just a tree of accounts and transactions, and basically any structure you want you have to make yourself. I'll try to type an answer, though. – Peter Cooper Jr. Mar 3 '17 at 14:53
2

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.

  • Maybe I do want a 2-axis solution. When I am entering info for "checking account Property A", I'd rather not have to specify the expense (in transfer field) as Rental Expense Property A:utility:gas... but instead as Rental Expense:utility:gas. I keep thinking in terms of having just one account per property, with the generic expenses (non-property specific) accounts. Maybe can't be done. If it were in SQL it might be like join some tables together (Property table, Expense table, Income table) and do SELECT sum(expenses), sum(income) FROM linked-tables WHERE propertyID = "A" GROUP BY PropertyID – pholck Mar 3 '17 at 15:40
  • Specifying the transfer to include the property probably isn't as painful as you expect, since most transactions are to the same payees and for the same things (so autocomplete based on name or hitting the duplicate button is how most entries happen). Though if you usually think of your stuff in a database-centric way, perhaps the tool you really want is an Access database or the like? – Peter Cooper Jr. Mar 3 '17 at 15:59
  • 1
    Perhaps. Could use MySQL backend to generate reports, but takes some effort. So in summary, I think you're saying I can't generate an income/expense report PER CHECKING ACCT (if I think of each property as a separate "checking acct"). Need to have specific expense/income accts per property, or specific property accts per expense/income acct. Thanks a bunch! – pholck Mar 3 '17 at 16:49
1

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!

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .