On Mon, Feb 1, 2016 at 1:13 PM, John Hendy <[email protected]> wrote:
> Greetings, > > > It's a fresh year and I've been seeing ledger come up on the Org-mode > mailing list for some time and decided to give it a try. I'm coming > from Moneydance and just wanted to get away from the tedious GUI > method of adding information, as well as have flexibility to generate > my own reports/visualizations with python or R, etc. [1] > > Consider that I'm about a week into reading through docs here and > there during evenings. My first step was going to be importing a > downloaded .csv from my bank to get started. I'm still trying to > verify I get the terminology, so I'll use this from the manual: > > From 5.1 Basic format: > ``` > This transaction has a date, a payee or description, a target account > (the first posting), and a source account (the second posting). Each > posting specifies what action is taken related to that account. > ``` > > From 7.2.1.2 The convert command: > ``` > The fields ledger can recognize contain these case-insensitive strings > date, posted, code, payee or desc or description, amount, cost,total, > and note. > ``` > > For my purposes, I import my finances primarily to "categorize" (what > I believe here is called adding an account) and assign a payee so that > I can track my spending against a budget. So, I'm surprised there's no > special column keyword I can add for "account". It appears that all I > can do is pass, say, `--account "assets:checking"` to have ledger know > it's against assets:checking? Is that correct? > > From trying to google "import csv account ledger" or similar > variations, I've been surprised that the only tools to do something > like this appear to be interactive one-trans-at-a-time programs like > icsv2ledger and reckon (granted, they can learn or follow rules). I > could quickly go through my bank's .csv and add exp:food:dining, > exp:auto:fuel to my ~100 transactions a month and have those imported > just like the other column data. > Keep in mind that part of the process of importing (they like call it "reconciling") involves - Manually reviewing the transactions for correctness or fraud - Merging new transactions with previous transactions imported from the other side (e.g. a payment from a bank account to pay off on'es credit card will typically be imported from both the bank AND credit card accounts; you must merge the corresponding transactions together) - Assigning the right category (you can automate this with a script I suppose; frankly it's not much work, I do all of mine manually with the help of auto-completion from Emacs, which is the most important feature IMO) - Moving the resulting transactions to the right place in your file. - Verifying balances visually, or inserting a balance directive which asserts what the final account balance should be (for correctness) after the new transactions. If you do it often enough and you have editing chops, you get used to the dance and it's a breeze. I think the fourth step can be hypothetically solved using heuristics. I feel like I must be missing something with respect to getting the > from/to accounts added to the bank data. > > Perhaps to take a step back... > - are the majority of folks writing their transactions by hand in ledger > format? > Can't say about others, but for me I want to say that about half the importing is semi-automatic. - Credit cards and banks import from downloads but I need to categorize manually (as described above), fairly good quality downloads. - Investment accounts fully automated buys but I need to manually edit sales in some accounts. Great quality of downloads. - Payroll stubs and vesting and a few other things are provided only as PDFs and I don't bother trying to extract (though I've made some headway towards this, it's incomplete; it turns out fully automating table extraction from PDF isn't trivial. The best OSS solution is TabulaPDF by far but you still need to manually identify where the table is). - Cash transactions: I have to enter those by hand. I only book non-food expenses as individual transactions directly, and for food maybe once every six months I'll count my wallet balance and insert one transaction per month to debit away the cash account toward food. If you do this, you end up with surprisingly little transactions to book manually, maybe a few/week. I suppose it could depend on lifestyle choices. It takes me less than 1 hour/week to run through the active accounts, usually first thing Saturday morning when I get up. Most of the pain is logging with user/passwords into the various institutions and clicking the right buttons to generate the downloaded files. Extraction and filing is automated using importers I wrote against LedgerHub. Less active accounts are updated every quarter or when I feel like it. > - is there some better way to import bulk data (e.g. via ledger's > convert function) and post-edit once it's in ledger format? It seemed > a .csv in LO calc was pretty convenient vs. scrolling through a long > text file - any other pointers along the above lines would be most welcome. > Check out LedgerHub for ideas. Original design doc: http://furius.ca/ledgerhub/doc/design Post-mortem: http://furius.ca/ledgerhub/doc/postmortem The project is being killed right now, rewritten much better and simpler and migrated into the Beancount project; if you do end up looking at the code make sure you're checking out the "stable" branch, it's a bit of a riot on the default branch right now, it will be broken. Essentially, I'm defining a config (in Python) as a list of "importer" objects and boil the process down to three steps: 1. Identify: Given a messy list of downloaded files (e.g. in ~/Downloads), automatically identify which importer is supposed to handle them 2. Extract: Extracting transactions and statement date from each file, if possble 3. File: Filing away the downloads to a directory hierarchy which mirrors the chart of accounts, for preservation, e.g. in a personal git repo. You could think of adding 0. Fetch: Automatically download the files but that's too hard. Personally I just don't have the stamina to implement this for myself. Given the nature of today's websites and the castles of JavaScript used to implement them, this would be a nightmare to implement for too little payoff. I love the idea of full automation, but I just don't have the time. Note that if you don't mind the nature of their business (they sell your data), you could potentially try to use Yodlee to pull much of it from a single place. In any case, you can't really get away without writing at least some code--it's just not realistic, the inputs from different people vary too much. There's very little shared code out there (just basic codes for CSV files, like the ones you mention) but too few users that share the same accounts to generate the critical mass needed for reuse. A while back I created the LedgerHub project to host shared importer code and provide a framework for doing the above, but never received much contributions and honestly I didn't put the care and quality attention to it I should have. More importantly, regression testing for those importers is most easily carried out using actual downloaded files compared to a corresponding expected output, but these files don't share well (they contain lots of personal data) so one ends up with two repositories anyhow. And besides there are several design decisions in some importers that may not please every user, in particular about how you choose your accounts for investments (there are degrees of freedom), so even sharing is not entirely an obvious win. By the way, I've found that regression testing is the _key_ to maintaining your importer code, because those importers are often written against file formats with no official spec and unexpected surprises show up routinely (e.g. I have XML files with some unescaped "&" characters, which require a custom fix "just for that bank", for instance, lots of nasty surprises), so you really need to be able to reproduce your tests. I think I have to make at least _some_ fix to an importer about once/month, and that sinks maybe a half-hour (involves adding the new file which makes it break, fix the importer code, and potentially update the older expected files for changes). I hope this helps give some color to the process, I tried to search the list for more of this sort of question, so > forgive me if I've missed something. Replying with links pointing me > in the right direction would be plenty sufficient if this has already > been discussed! > > > Thanks! > John > > > [1] http://moneydance.com/ > > -- > > --- > You received this message because you are subscribed to the Google Groups > "Ledger" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- --- You received this message because you are subscribed to the Google Groups "Ledger" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
