Quicken [arrgh.]
Saturday, 16 August 2008 12:29 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
daI'm trying to figure out how much money each of dan and me have put toward our expenses since we moved here 8 years ago. In a rough sense, to the nearest thousand dollars. I'm drowning in a sea of data.
And I want to boot Quicken to the curb.
The first problem with Quicken: exchange rates. It doesn't keep historical rates. When we moved here, the US dollar was worth $1.50 CAD. I put a lot of money in then. Now the US dollar's worth $1.06 CAD. Quicken, amazingly, reports it all at the current exchange rate. OK, so between both of us there are only about 25 contributions in USD of a sizable amount. So I can maybe do them each manually outside Quicken, though I wish there were a way to do that and use Quicken as the authoritative data without setting up parallel "translated" transactions. Ugh how annoying and inelegant.
The next problem is partly conceptual on my part, but Quicken doesn't make it easier. I won't be offended if you skip reading this bit; my eyes glaze over when I try to think about it. My hope is that writing it out will help, and maybe somebody else will be clear enough on it to say "yes, that's right." Here are two simple situations and one that wraps my head in knots: [or, did, before I wrote it out...] [ack. but it doesn't work right. ack.]
We have a Cash account, which I've used if someone pays for a joint expense on a personal account (so, otherwise not recorded in our joint expenses). That's straight-forward and the reports Quicken will produce look sensible for our spending. I even realized I can do the Double Entry Thing and zero-out the Cash account by making a balancing entry to the Cash account but with a category of, say, "contribution by DA" so I can see that I made an extra contribution to the joint accounts.
Then we've got instances where one of us paid for something off the joint accounts that's actually a personal expense. So I can make a transfer from the joint bank account to the cash account; then a balancing entry in Cash with a sensible comment and the category of "contribution by DA" and a negative amount, so it's actually subtracting from my contributions.
[This doesn't work properly! Quicken puts the damn cash transfer the wrong way around! It looks like I would've needed to swap every single "Spend" and "Receive" in the Cash account to make transfers work, but then the first case above without a transfer fails!]
The third instance is where, say, dan pays for something that's my expense not joint. Such as when my health insurance was directly debited from his salary. This is a conceptual mess for me. (If I figured this out, I expect I'd probably be more useful when helping settle bills with friends at dinner, too.)
If this were double-entry accounting, I think it would be: a credit to "contribution by db" and a debit from "contribution by DA". It doesn't affect any other joint accounts. The money goes from him to me. That's accurate. Yes?
In Quicken, this looks to me like two transactions in Cash, one with the category "contribution by DA", as an expense, and one with the category "contribution by db" as an amount received.
That makes some sense. And the report comes out OK; but for some reason, for the last decade I've been doing it differently; and I won't even try to explain it because it sounds wrong to me now.
Which suggests what I wrote above helped me figure out that this is the right way to do it.
Right?
I considered just deleting the majority of this post, but I'm not, because it amuses me now.
[And I'm so not amused any more. I still need to know if my logic is correct with the third case; but I also need to know what I should be doing for the second case. Grrr.]
And I want to boot Quicken to the curb.
The first problem with Quicken: exchange rates. It doesn't keep historical rates. When we moved here, the US dollar was worth $1.50 CAD. I put a lot of money in then. Now the US dollar's worth $1.06 CAD. Quicken, amazingly, reports it all at the current exchange rate. OK, so between both of us there are only about 25 contributions in USD of a sizable amount. So I can maybe do them each manually outside Quicken, though I wish there were a way to do that and use Quicken as the authoritative data without setting up parallel "translated" transactions. Ugh how annoying and inelegant.
The next problem is partly conceptual on my part, but Quicken doesn't make it easier. I won't be offended if you skip reading this bit; my eyes glaze over when I try to think about it. My hope is that writing it out will help, and maybe somebody else will be clear enough on it to say "yes, that's right." Here are two simple situations and one that wraps my head in knots: [or, did, before I wrote it out...] [ack. but it doesn't work right. ack.]
We have a Cash account, which I've used if someone pays for a joint expense on a personal account (so, otherwise not recorded in our joint expenses). That's straight-forward and the reports Quicken will produce look sensible for our spending. I even realized I can do the Double Entry Thing and zero-out the Cash account by making a balancing entry to the Cash account but with a category of, say, "contribution by DA" so I can see that I made an extra contribution to the joint accounts.
Then we've got instances where one of us paid for something off the joint accounts that's actually a personal expense. So I can make a transfer from the joint bank account to the cash account; then a balancing entry in Cash with a sensible comment and the category of "contribution by DA" and a negative amount, so it's actually subtracting from my contributions.
[This doesn't work properly! Quicken puts the damn cash transfer the wrong way around! It looks like I would've needed to swap every single "Spend" and "Receive" in the Cash account to make transfers work, but then the first case above without a transfer fails!]
The third instance is where, say, dan pays for something that's my expense not joint. Such as when my health insurance was directly debited from his salary. This is a conceptual mess for me. (If I figured this out, I expect I'd probably be more useful when helping settle bills with friends at dinner, too.)
If this were double-entry accounting, I think it would be: a credit to "contribution by db" and a debit from "contribution by DA". It doesn't affect any other joint accounts. The money goes from him to me. That's accurate. Yes?
In Quicken, this looks to me like two transactions in Cash, one with the category "contribution by DA", as an expense, and one with the category "contribution by db" as an amount received.
That makes some sense. And the report comes out OK; but for some reason, for the last decade I've been doing it differently; and I won't even try to explain it because it sounds wrong to me now.
Which suggests what I wrote above helped me figure out that this is the right way to do it.
Right?
I considered just deleting the majority of this post, but I'm not, because it amuses me now.
[And I'm so not amused any more. I still need to know if my logic is correct with the third case; but I also need to know what I should be doing for the second case. Grrr.]
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: Saturday, 16 August 2008 10:04 pm (UTC)That makes a transaction in the report that has the proper negative/positive, though it's in the wrong damn section (expenses).
I wonder if any other consumer-grade accounting program does this better. Or if I'm just being stupid.
no subject
Date: Saturday, 16 August 2008 10:32 pm (UTC)I hate quicken, BTW. But I do understand double entry. What you want to do should be trivial, but of course it isn't because Quicken is a load of sheep shit.
no subject
Date: Saturday, 16 August 2008 10:33 pm (UTC)no subject
Date: Saturday, 16 August 2008 11:21 pm (UTC)no subject
Date: Sunday, 17 August 2008 12:24 am (UTC)I've had frustrations with doing some things as transfers and others as categories, so I gave up on using transfers as the main record. Quicken wants me to put things in categories. Dealing with the Cash account, I am using a second transaction to have a category and make the cash account come out to zero. (Also, so I can have a non-transfer transaction to hang a class off of- I didn't mention, but I've also been using classes to store whether something's due to dan or myself, and putting a class on a transfer seemed to make things too confusing.)
no subject
Date: Sunday, 17 August 2008 12:25 am (UTC)I wish iBank would successfully run.
*scowl*
no subject
Date: Sunday, 17 August 2008 02:10 am (UTC)Indeed. And if you ever try to download "Quicken-ready import files" from financial institutions, they certainly aren't double-entry. Not only that, but once imported, your data is usually hosed, because what one bank thinks happened on the 16th happened on the 17th on the other end, or vice versa.
I found Quicken to be nifty about ten years ago, when I really had no clue what I spent or how my investments were doing. Then I found I was spending eight hours a month just bookkeeping. These days I glance at monthly statements and just try to recall whether this was a big spending month or not. Unless there have been more than two large ticket items, my expenses are fairly stable.
(it was cool to see "ooh, I spend X% on fresh foods and X*7% on books", but didn't really tell me much I did not already know)
no subject
Date: Sunday, 17 August 2008 03:08 pm (UTC)no subject
Date: Monday, 18 August 2008 12:52 am (UTC)Export the weekly (or monthly, since it is over eight years) average exchange rates from yahoo (or your choice of financial sites). This will come as two columns, one dates and then one rates (columns A & B). Add a column of zeros to the right of the exchange rates (C). In the weeks where you actually paid something in USD, enter an amount instead of a zero, with it being negative for you, and positive for DB. Then in the first row with data in column, multiply B*C and copy the formula down. Then auto sum column D. Wha-la! The net amount of canadian dollars, either negative if you paid more, or positive if DB paid more.
(Do I spend a lot of time in my professional life doing this sort of thing or thinking about how to do this sort of thing efficiently? Yes, yes I do.)
no subject
Date: Monday, 18 August 2008 10:57 pm (UTC)Don't get me started on data import.
If any of these programs wasn't a pile of horse-doodoo, I would be ecstatic.
no subject
Date: Monday, 18 August 2008 11:08 pm (UTC)Unfortunately, it sounds fairly buggy still. And everybody seems to want a pony when it comes to money-management.
I forget if I had a larger point when I started this comment. :)
no subject
Date: Monday, 18 August 2008 11:20 pm (UTC)I suppose instead of writing a google-docs macro to grab specific dates' rates, I could've probably done with monthly rates... but yahoo finance told me they weren't any help for historical USD/CAD rates, so I gave up on 'em.
Do you have any thoughts about the rest of my post- I'm still not sure whether I have the right thinking on "the third instance". Let alone what Quicken wants me to do for the second instance. (I won't be at all offended if the way I wrote it makes you go cross-eyed and it's impossible to answer given what I said.) But yeah. I'm not too fond of Quicken right now...
no subject
Date: Tuesday, 19 August 2008 01:15 am (UTC)And Quicken is, in fact, even worse on a Mac than a PC. Even though *most* things are *easier* on a Mac than a PC. Grrrrr.