Post by Ken GanshirtJust idle curiousity, Stan ... how do you share your MD data with others
now? Are you still using QIF or something else?
...ken...
Well, I mentioned in another post that I had become annoyed with
transactions that had "." for "Check Number" (or whatever the field is
called), so I dumped all the data out to an XML file and replaced the
periods with nothing (i.e., removed them). This is the sort of thing I've
been wanting to do for years but couldn't.
I dabbled with Moneydance's ability to import text files, but having
already done the work to spit out QIF files that MD imports just fine, I
found that it was simpler to just stick with the QIF format.
There is sort of a long answer besides this, so you might want to take a
No-Doz, or take advantage of this sure cure for sleep deprivation...
I have had several Access databases over the years and gradually rolled
most of them into one that performs several functions. Okay, I say
"still," although when I upgraded to Office 2003, it broke about 3/4 of the
various databases I use, but don't get me started on Microsoft next!
I don't trust the idea of connecting to my FI's and letting them put
transactions directly into my financial software. I want to maintain my
own data where only I can touch it, and then I compare it to what the FI
says. That doesn't mean I want to enter every transaction by hand,
however. Here are some of the ways I pass data around. Most of this has
to do with maintaining my checking account.
I download a QIF file from my bank and load it into my database. I have
added a duplicate transaction avoidance algorithm (I did this before
Quicken had it, and kept thinking, "hey, this isn't rocket science"), so I
just download all available transactions and anything that's already in the
database doesn't get loaded. I built this shortly after I opened the
account, almost 10 years ago, so it has every transaction that has ever
been posted to my checking account. Part of my motivation is that my bank
only shows the last 90 days or something like that, and I didn't want them
to roll off.
I can use this to assist me in reconciling my account. The sum of all
transactions is the current balance according to the bank. The database
knows what transactions I've just downloaded, and I have a macro that I run
when I reconcile to my monthly statement, so it also knows if I've gotten
ahead of that, so I know which transactions to toggle back to uncleared,
run reconciliation to the statement balance, then toggle them back and
reconcile to the current balance.
I can go through and check off transactions to export to a QIF file and
import them into my financial software. I can select and do this for only
deposits that haven't been done before, or billpay transactions, or use any
other criteria I like. Some billpay payees are set to create duplicate
transactions with flags that make them obvious duplicates in the financial
software, which allows me to easily compare one above the other, what's in
the financial software to what the bank shows (mainly dates, amounts, and
of course making sure that they exist at all). What comes in from the bank
also has a transaction ID, which I put in the memo field, confirming that I
actually did generate this transaction at the bank (sometimes I put
transactions in the financial software that I *plan* to create at the bank,
and of course there are transactions automatically generated in the
software that may or may not correspond to anything that actually happens
at the bank). What comes in from the bank does *not* have categories and
splits on it, which takes us back to not simply using what the bank gives
me, but creating transactions in the financial software and comparing it to
what the bank has.
It's easy enough to copy the transaction ID from what's in the QIF file,
then, and paste it into what didn't come from the QIF file. However, as I
mentioned some weeks ago, I can't make cut/copy/paste functions that work
everywhere else on my computer work at all in Moneydance, and MD support
has stopped replying to my email, doesn't even acknowledge that they might
be trying to find a solution, and I'll admit that I'm a little peeved at
this, but then I remind myself that Intuit's support is about 100 times
worse than that, as I grumble and manually type everything in... Okay, I'm
digressing.
Billpay transactions come in separately because the QIF file from the bank
only contains transactions that have already been posted, whereas I want to
see transactions that have been scheduled but not yet posted. On the web
page where my billpay transactions are all listed, I highlight the table,
copy and paste into a text file. This is loaded into the database, which
sorts out all the crap and creates a QIF file as I already described. This
functionality was actually added on to another function that I had built
earlier...
I got tired of missing payments because bills got misplaced or lost in the
mail or whatever, so I created a list of all the bills we pay every month
and started checking them off as I scheduled payments or cut checks or
whatever. Eventually, only scheduled payments were involved because I
don't cut checks for these things anymore. Eventually, I put this into
Access, and each month I generate a report that lists all the bills we
expect to pay each month. By using the "screen scraper" technique I
mentioned, I was able to make the report more sophisticated, so that as
time goes by, everything is blank, and then as payments are scheduled,
payment dates, amounts, and transaction ID's are populated. Anything
that's still blank hasn't been dealt with yet (I put in zeroes if there is
no amount due, so I can see that it doesn't *need* to be dealt with).
Gradually, I added routines to handle it when I unschedule something at the
bank, or change the date or the amount. Human error is gone from the
picture, which was my intent. This is the biggest part that is now totally
broken as a result of my MS Office "upgrade" and it makes me want to cry.
Eventually, I am rebuilding my broken databases in Tcl and phasing out
Access altogether, but taking this on only highlights how many databases I
use in my life and how intricate all the processes are.
Finally, I created a separate database for inputting transactions. I
didn't much care for Quicken's interface, and this allows me to easily move
around, copying data, inputting in the sequence I choose, and so on. I
just find that it's easier to type into what I built than to use Quicken
directly, so I created this, and it spits out a QIF file that I can import
into any account in Quicken. This is especially handy now that we use a
credit card with advantage miles for most of our purchases.
Oddly enough, I find Moneydance less annoying than Quicken to use to input
data directly. I'm not sure why this is, and there are some annoyances, to
be sure. I'd like to be able to control what previous information is used
to auto-complete a transaction (for example, the transaction ID I put in
the memo, or for that matter, just about anything I put in the memo,
generally applies to *that* transaction only). I'd like to be able to use
"Enter" to move from one field to the next, rather than "Tab," and this old
habit has a tendency to create blank transactions, which I think Moneydance
should trap in any case. And when I get to the "Number" field, and type
"bi," it will autocomplete as "biLLPAY," whereas I think it should use
"BILLPAY," because that's how it is in the list of choices. But that's
*really* nitpicky...
At any rate, you can see that when Intuit decided to remove the
functionality of QIF imports, Quicken had to go...
Stan