quote:
My next step is to write a little script to visit each Amazon item page to scrape the price and department, which I can use to assign each purchase to the proper expense account.

Quick update here:

I was NOT able to scrape Amazon's site for price and category info because they have pretty robust systems in place to prevent scraping. They do have an API for getting product info, but you need an Associates account for that. I may get that set up eventually, but in the meantime I just used that Orders extension to download a CSV, and I made a rules file to process it just like I did with the transactions CSV files that I downloaded from my banks. It works beautifully, and I was able to categorize 304 individual Amazon purchases YTD in very little time.

I spent 1.04 hours (tracked with timeclock format in my accounting system) researching and writing the code to attempt to scrape Amazon's site. As I mentioned, this was a failure and a "waste" of time, but I did learn some things about writing Python. Specifically, I learned how to write a script that takes command line arguments, processes a csv file, searches and returns google results, and downloads/processes a webpage. I think that was an hour well-spent.

Then I spent 0.53 hours (again tracked with timeclock accounting) writing rules to categorize Amazon purchases based on item descriptions. These rules are essentially just looking for keywords and posting to the right accounts. And again, that was half an hour to process 304 purchases, which would have taken me at least 2 or 3 hours using mint.com and matching transactions with amazon orders manually. Further, since the rules are already written, they will automatically pick up future purchases. Whenever new purchases fall through my rules to an Uncategorized account, I will just add a few more keyword rules to pick them up for next time. I have reduced what used to be a 20 or 30 minutes process each month down to about 3 minutes per month from now on. I love it.

So about the time tracking feature, these tools don't care whether you're moving around dollars or donuts, and they have extra features specifically for moving around hours/minutes/seconds. You just record your in/out times in your journal file, and which account you are posting the time to, and the time shows up in your reports alongside dollars. You can also easily filter by currency/commodity, so you can generate reports that include only dollars, or only hours, or only euros, or any combination.

Tracking time is handy not only so you know what you've spent your time doing, but also you can of course track billable hours if that's part of your business.

And you can *budget* time! I wasn't sure it would work (though I couldn't think of a reason why it wouldn't), so I just added some hours into my budget file and sure enough the time spent/budgeted showed up in my reports. Maybe I will budget X hours per week that need to be billable, Y hours for exercise, Z hours reading, etc. Pretty neat.

Tracking time like this might sound tedious, but I have an idea to make it super easy. I've been working with Flutter making mobile apps, and I can easily knock out a simple timeclock app in a couple hours. All it needs is a dropdown to choose an account, and a big clock in/out button. It will just append the timestamps to the file, which is saved on dropbox or whatever. That will auto sync to my desktop, where the time is automatically picked up in any future reports on my laptop. I also have unlimited options for further processing of the data to give myself access to reports remotely.

1 ...

Report Post

Posted by Korkstand

Member since Nov 2003

28708 posts

Posted on 9/30/20 at 2:01 am to Korkstand

And I know I've already written a border wall of text in here, but here comes another mile. Consider this my contribution to the plaintext accounting community, to make up for the multi-million dollar advertising budgets of the big boys.

I just want to kind of lay out my file structure in case anyone is interested in all this, but who might be kind of lost in how to get started. You are free to do it however you want, but this is what I'm zeroing in on as a nice solid system.

I'm using hledger.

/
2020.journal -> main file that reports are run against. includes (links to) all other journals, settings, and budgets files. only entry stored in this file is yearly starting balances.

accounts.ledger -> chart of accounts

budget.ledger -> budgets

commodities.ledger -> defines commodities ($, hours, any other arbitrary units I want to track)

personal.timeclock -> personal time journal entries

business.timeclock -> billable or otherwise business-related time entries

imports.ledger -> generated automatically via script which scans the /journals/ folder for all *.journal files, so that I can add journal files without having to remember to import them. this file is in turn imported by 2020.journal.

/journals/

bankname.csv -> CSV file downloaded from bank

bankname.rules -> rules file that tells hledger what the fields are, as well as simple matching rules that choose which account each transaction should post to depending on keywords and other rules

bankname.journal -> ledger entries created automatically from CSV file processed with rules file.

... repeat above for each bank, paypal, amazon, etc ...

cash.journal -> cash or other transactions that are for whatever reason not recorded electronically or not available for csv download

checks.journal -> since physical checks appear in bank csv with only a check # description, entries in this file are added manually to move money to the correct accounts

corrections.journal -> if for some reason I am not able to write a rule to properly categorize a transaction, or for any other special or rare circumstances such as splitting a transaction, I manually write ledger entries which move the money from the automatically chosen account to the proper account(s). this keeps my rules simple, and prevents my changes from being lost when I regenerate a bank.journal file from csv source

/scripts/

import.sh -> scans /journals/ for .csv files, matches them to .rules files, and runs the appropriate hledger commands to produce .journal files. rewrites imports.ledger to include all .journal files

outdated.sh -> scans /journals/ for .csv files, emails me a report about which accounts have no transactions newer than X days

reports.sh -> runs hledger balance command (and maybe others) and emails me the results

... any other python or shell scripts that I dream up (favorite report commands, automatic balance checks, projections, etc) ...

/templates/

... to be developed, most likely will be html/css/image assets which will be used by scripts to generate PDF invoices from journal entries ...

/invoices/
__/exports/

*.pdf -> automatically generated by script from invoice journal entries, to be reviewed

__/sent/

*.pdf -> moved here from /exports/ after sent to client

__/paid/

*.pdf -> moved here from /sent/ after paid

The workflow is as follows:

1. Maybe once a week/month I'll spend a few minutes downloading my transactions in csv format from each bank and save them in the /journals/ folder.

2. ?

3. Profit.

And that's pretty much it. I'll get emails on whatever schedule I set, which will tell me which banks may need new transactions downloaded, and also a balance report which will tell me how I'm looking on my budgets, as well as whether I need to take a look at any transactions for manual adjustment or if I need to adjust my import rules. As far as personal finances, I can't see spending more than 30 minutes per month from here on out. That is a far cry from what I've been doing with mint.com, where every time I logged in I had to reconnect an account or two, look at the budget screen to see if anything is out of whack, scroll through hundreds of transactions to fix what it categorized wrong, filter the uncategorized stuff and load up amazon order history to manually match order totals. Then get pissed off because it's impossible to fix budgets with rollover retroactively on there. Such a pain in the arse, and I'm glad to finally have all that automated away.

How I handle duplicated transactions (ex. CC company CSV has payment to Amazon, Amazon CSV has record of payment for same thing):

I have to process both files because each has different information that I need, but the logic to combine the data into one "clean" transaction is far too complicated, especially when you start dealing with multiple sources of duplicate transactions. The solution is surprisingly simple. In my case, I can determine via my rules file where most of my CC transactions should go, just based on whether the payee is a restaurant, gas station, retail store, grocery store, etc. The main problem is the hundreds of Amazon purchases totaling thousands of dollars, which I would also like to automatically categorize. But in my CC processing rules, I just dump all Amazon purchases into an Uncategorized:Amazon account. Then my Amazon rules file takes those amounts from the Uncategorized:Amazon account and puts them into the appropriate Expense accounts based on the more detailed item descriptions in the Amazon purchases CSV file.

So the end result is each transaction does end up in my collective journal twice, but they are not double posted to an Expense account. Instead, I have a temporary holding account that should always balance to zero. A similar process works for making payments on the credit card. Since the transaction appears in both my checking account and the CC account, I can't post either directly from checking to the credit card. Instead, the payment from checking goes into Assets:Transfers, and on the receiving end the CC rule pulls from Assets:Transfers and into the CC account. I found that mint.com struggled with these types of transactions sometimes, often doubling things and requiring manual intervention.

That's about as far as I've gotten, but this will surely evolve and grow as the years go by. I feel like I've already got a pretty comprehensive system, and the best part IMO is it will last for the rest of my life without costing a dime (and while saving me a lot of time). hledger is open source and does not depend on a 3rd party, so even if the whole world goes to shite, as long as I have a working computer I will be able to compile and run it. Same goes for my data since it is all plain text.

And to anyone who made it this far,

1 ...

Report Post

Posted by Wilson

Metairie

Member since Jul 2011

240 posts

Posted on 10/11/20 at 11:06 pm to Korkstand

I'm not sure you would call what you're doing "accounting". I would classify it more as tracking personal spending.

I'm curious exactly what problems you had with Quickbooks. I assume you had the desktop version (online sucks). Reporting is actually one of the things that QB desktop does extremely well. I can pretty much run anything kind of report I want with filters (not so with the online version).

Accounting programs have to inherently balance. What happens in your program when you make a typo with a number? Without reconciliations and balance sheets, I'm not sure how you'll know if everything is correct.

1 ...

Report Post

Posted by Korkstand

Member since Nov 2003

28708 posts

Posted on 10/12/20 at 12:11 am to Wilson

quote:
I'm not sure you would call what you're doing "accounting". I would classify it more as tracking personal spending.

It's double-entry and reporting options are limitless. I'm no accountant, but I'm really not sure what else is required to qualify as "accounting".

quote:
I'm curious exactly what problems you had with Quickbooks. I assume you had the desktop version (online sucks).

Problem #1 for me is I run Linux, so QB desktop is a no-go there. I never tried the online version, but I have tried a few other online accounting packages and they all have similar issues (reporting sucks and is limiting, the interface is slow, etc). Also QB desktop is kind of slow, licensing is complicated, and I just generally don't like it.

quote:
Accounting programs have to inherently balance. What happens in your program when you make a typo with a number?

The tools force each transaction to balance. If you leave the amount for a posting in a transaction empty, the balancing amount is assumed. This way a typo is not caught, so I type out the amount going to/from each account, so all typos are caught every time I run a report. You can also intentionally make unbalanced "virtual" postings, used for budget accounts or things like that. You can run reports that include or exclude virtual postings.

quote:
Without reconciliations and balance sheets, I'm not sure how you'll know if everything is correct.

Displaying account balances is very simple, and that's really the main thing that these tools do. Print balances, or print the journal register ("print" just means show in the command window, not actually out on paper). These are the main reports, which you can filter by date, accounts, currency, etc. Your balances can be printed monthly, quarterly, yearly, etc. There are tons of options, and you just compose them as you need.

You can mark postings as cleared or pending if you want, and that is yet another option for printing reports to help you reconcile. And you can do balance assignment transactions, and the tools make the appropriate postings to ensure the given account is a given amount on a given date.

So, basically, you know that everything is correct because you make it correct.

The tool I use also provides easy reports for the standard balance sheet, cash flow, and income statement. Which of course are similarly composable with the array of filtering options if needed.

This is obviously NOT for everyone. There is a somewhat steep learning curve, but I have learned a lot about accounting in the process.

This post was edited on 10/12/20 at 12:18 am

Page 1 2