“My bank exports transactions but they’re all positive so there’s no way to import and get Debits…”
or
“My credit card charges are positive and the credits are negative, just the opposite of what I need…”
Star Trek’s Bones once said, “I know engineers, they love to change things.” And, having seen at least forty-two thousand more statement files than your average bear, we’re highly inclined to agree. We’ve borne witness to wacky date formats, data in the wrong field(s), deposits with negative amounts and debits with positive amounts, and even all positive amounts – no matter what kind of transaction. You name it, it’s out there. And, you’re probably reading this right now because you’re in it.
Enter CheckBook’s Import Options > Data Layout.
As you import a text or CSV file into an Account for the first time, you’ll want to set up that Account’s Import Options > Data Layout. That’s the bit at the top of the Import Options window, right after you select the file to import. Once you get this part right, you won’t have to touch it again unless your bank changes how they export. 🤞
Each row in Data Layout is the raw data for a single transaction, what will become a CheckBook Entry, while the columns are for the various details of each transaction. If you know spreadsheets, you’ll feel right at home. Banks usually export at least four columns per row: a date, amount, check number (if applicable), and some kind of half human language, half reference number mashup that might give a general idea of what the transaction was for. Some banks give a bit more, like a column for charges and a column for credits, or a column for a running balance. It just depends on how many Twinkies the engineers at your bank gobbled the day they specced their export project. What matters is you’ll need to tell CheckBook what kind of data is in each column so it can make Entries out of all that. That’s what the little menu buttons at the top of each column are for: you pick the type of data in the column, like Date, To/From, or Amount, and off you go…except for those amounts that don’t make sense.
Some banks really will give you all positive numbers, even for your debits or charges. Others do the aforementioned two-column deal for debit and deposit amounts. And credit card banks love to make charges positive and credits negative. No worries, though: you can tell CheckBook to flip those amounts around as needed.
If your bank provides a single column with all positive numbers
These banks usually include another column to tell you which rows are for debits and which are for deposits. In Data Layout, click the menu button above the column with all the amounts, click the Amount menu item, then put a checkmark in the Import Entries whose X field is Y as Debits checkbox further down the window. Now, the first menu button in that checkbox will be a list of column names. Click the first menu button, then click the name of the column that has the unique detail that tells you the row is a debit or a deposit. The second menu button will be a list of all the values in the column you just chose. Click the second menu button, then click the debit-specific value. For example, if your file has Date, Amount, Type, Check #, and Payee columns, you’ll set up the window like so:
If your bank provides two amount columns
In Data Layout, click the menu button above the column with all the positive amounts, then click the Positive Amount menu item. Now, click the menu button above the column with all the negative amounts, then click the Negative Amount menu item.
If your credit card bank provides a single column with amounts that are opposite what you need
In Data Layout, click the menu button above the column with all the amounts, then click the Flipped Amount menu item.
How’s that work for you? If you need a hand, we’re ready to help at support@splasm.com!
(And now you know the secrets. Blab them all you want!)