Getting started; assigning accounts to bank .csv data

[John Hendy]

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.

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?
- 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.


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/

[Craig Earls]

I have a custom elisp mode that sucks in csv from my bank and sticks it in my ledger. The emacs ledger mode has enough built in autocompletion that hand rntering a few xavts here and there isnt onerous. Its all about the editor. 

--

---
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 ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Craig, Corona De Tucson, AZ
enderw88.wordpress.com

[Martin Blais]

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/

[John Hendy]

On Monday, February 1, 2016 at 6:03:36 PM UTC-6, Craig Earls wrote:

I have a custom elisp mode that sucks in csv from my bank and sticks it in my ledger. The emacs ledger mode has enough built in autocompletion that hand rntering a few xavts here and there isnt onerous. Its all about the editor. 

I'll have to check out ledger mode in emacs. I didn't get to that part in the ledger docs yet. I'm already an emacs user, so at least I don't have to add that to the learning curve!

Thanks,

John

 

To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[John Hendy]

Thanks for the awesome reply!

 

Keep in mind that part of the process of importing (they like call it "reconciling") involves

- Manually reviewing the transactions for correctness or fraud

I'll get there. For better or worse, I take the downloaded bank .csv as "truth" and am mostly interested in getting a better handle on what my money is used on, budgeting, planning, etc.

 

- 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)

Definitely. Moneydance allowed me to input an account, which would "link" the transaction. Then I'd have to delete or merge the other account's record of the same transaction.

 

- 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)

Huh. Yes, I'll definitely have to look into the emacs mode. I assumed once it was in ledger format it would be *a lot* harder to navigate around vs. just doing it while it's already in a spreadsheet format.

 

- Moving the resulting transactions to the right place in your file.

I'll have to look into this more. I get that this is the ledger list... but is beancount different in this respect? From reading your docs, it sounded like beancount didn't care about order. Or are there other reasons (besides date) that one would have to move transactions around?

 

- 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.

This is a helpful time estimate/reference. My main account (checking) has ~100 transactions per month. I don't mind categorizing them myself, but I hoped for a quick-ish way to do that. Typing "expenses:blah:blah" is pretty fast in a spreadsheet. While I *use* emacs, I'm no navigation whiz, and going to the right place in a block of text to type the same thing seems super tedious vs. a spreadsheet. Hence I was puzzled that I couldn't use ledger's convert command to just bring in accounts from the .csv along with the rest. After all, all the dates and amounts are there, one can add payees... why not accounts?

 

 

- 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.

Yeah, not interested in that. It's not a big deal to download the few files I need.

 

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.

That's okay, and I'm cool with trying some code. I primarily use R for data analysis/plotting, but have started getting introduced to python via Coursera recently and hope to dig in more. That's another thing that attracts me to beancount :) That said, these are more just general questions at this point. I'm amazed at how much documentation there is... but for a total noob, I can say it's a bit intimidating and kind of hard to know where one should start! Not to mention having questions and not being sure you're even searching for the right terminology to answer your question.

 

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,

Definitely, and sincere thanks for taking the time to give me some pointers!

John

[Martin Blais]

BTW, there are some ideas around about automatically merging two incomplete transactions. This problem is the dual of solving the issue of settlement dates, i.e., the problem being that the dates of each of the two sides may settlement on different days. 

See http://furius.ca/beancount/doc/proposal-settlement for some ruminations and scour the mailing-list, there is more discussion about this.

- 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)

Huh. Yes, I'll definitely have to look into the emacs mode. I assumed once it was in ledger format it would be *a lot* harder to navigate around vs. just doing it while it's already in a spreadsheet format.

Definitely not, text is there for your pleasure. You typically organize your Ledger input file in the order that makes the most sense for you (minus some constraints: Ledger will report the transactions in the order they appear in the file and the balance assertions are computed as such. Beancount sorts everything by date so order doesn't matter).

- Moving the resulting transactions to the right place in your file.

I'll have to look into this more. I get that this is the ledger list... but is beancount different in this respect? From reading your docs, it sounded like beancount didn't care about order. Or are there other reasons (besides date) that one would have to move transactions around?

In Ledger, the reporting is done in file order. Balance assertions as well.

In Beancount, order is by date, so you don't have to care about how you organize them.

I think - but I'm not 100% sure - that most Ledger users must store their input file by section, and in each section in date order, to minimize the number of out-of-order transactions if they print out a register.

I use org-mode to create sections and each section is stored in date order for some subset of accounts.

 

- 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.

This is a helpful time estimate/reference. My main account (checking) has ~100 transactions per month. I don't mind categorizing them myself, but I hoped for a quick-ish way to do that. Typing "expenses:blah:blah" is pretty fast in a spreadsheet. While I *use* emacs, I'm no navigation whiz, and going to the right place in a block of text to type the same thing seems super tedious vs. a spreadsheet. Hence I was puzzled that I couldn't use ledger's convert command to just bring in accounts from the .csv along with the rest. After all, all the dates and amounts are there, one can add payees... why not accounts?

You can probably script that away with a few rules. 

I admit that 100 txns/month is more than I have, and I might look into auto-categorizing most of it myself if I were in that situation.

Problem is, everyone's little scripts appear to have little in common.

 

- 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.

Yeah, not interested in that. It's not a big deal to download the few files I need.

 

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.

That's okay, and I'm cool with trying some code. I primarily use R for data analysis/plotting, but have started getting introduced to python via Coursera recently and hope to dig in more.

R won't be fun for doing this. R makes it a huge pain to even do the kind of data cleaning necessary for prepping data for analysis. Definitely use Python over it, you'll save a lot of time. If you really need some specialzed R module, you can create numpy arrays in Python and there is a module that allows you to invoke the R runtime with these. Best of both worlds, but I doubt you'll need it.

[Martin Blais]

BTW, here's an auto-generated example file that looks similar to how I organize mine using org-mode:

https://bitbucket.org/blais/beancount/src/tip/examples/example.beancount

[Erik Hetzner]

On Tue, 02 Feb 2016 19:48:06 -0800,
John Hendy <jw.h...@gmail.com> wrote:
>
>
>
> On Monday, February 1, 2016 at 10:41:26 PM UTC-6, Martin Blais wrote:
> >
> > - 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)
> >
>
> Definitely. Moneydance allowed me to input an account, which would "link"
> the transaction. Then I'd have to delete or merge the other account's
> record of the same transaction.

One needn’t actually merge these. Here is what I do:

2015/12/31 Credit card payment
Assets:Checking -$100
Transfer:AC->LCC $100

2016/01/01 Payment received
Liabilities:CreditCard $100
Transfer:AC->LCC -$100

Some kind person on the list pointed out this technique a while ago.

This makes import easier and allows for a difference in transit time. All
Transfer:* accounts should balance to $0, so you have an additional check that
everything is balancing out.

best, Erik
--
Sent from my free software system <http://fsf.org/>.

[Martin Blais]

I think it would be possible to process the stream of transactions and identify close matches based on common accounts and nearby dates, and automatically merge matching transactions into a single one, removing zero balances. The dual operation is assigning individual dates on postings of a single transaction and having the software split them up to obtain the result you describe.

In either case, matching transactions should be linked automatically and it should be possible to report on a set of matching transactions (a-la "bean-doctor linked").

[John Hendy]

On Tue, Feb 2, 2016 at 10:07 PM, Martin Blais <bl...@furius.ca> wrote:
> On Tue, Feb 2, 2016 at 10:48 PM, John Hendy <jw.h...@gmail.com> wrote:
>>
>> On Monday, February 1, 2016 at 10:41:26 PM UTC-6, Martin Blais wrote:
>>>
>>> On Mon, Feb 1, 2016 at 1:13 PM, John Hendy <jw.h...@gmail.com> wrote:
>>>>
>>>> Greetings,
>>>>

[snip]

>>
>>
>> Huh. Yes, I'll definitely have to look into the emacs mode. I assumed once
>> it was in ledger format it would be *a lot* harder to navigate around vs.
>> just doing it while it's already in a spreadsheet format.
>
>
> Definitely not, text is there for your pleasure. You typically organize your
> Ledger input file in the order that makes the most sense for you (minus some
> constraints: Ledger will report the transactions in the order they appear in
> the file and the balance assertions are computed as such. Beancount sorts
> everything by date so order doesn't matter).

So, using the demo.ledger file as an example, if I run `convert` on my
downloaded bank file, I'm going to get something like this:

2010/12/01 * Checking balance
Expenses:Unknown $1,000.00
Equity:Unknown

2010/12/20 * Organic Co-op
Expenses:Unknown $ 37.50 ; [=2011/01/01]
Equity:Unknown $ -225.00

2011/01/02 Grocery Store
Expenses:Unknown $ 65.00
Equity:Unknown

Would you just go through that and manually change all of those
categories in ledger-mode? I still like starting from the bank .csv,
as it's got transaction ids and the amounts already in there... all I
need to do is add categories. It appears that `convert` defaults to
the above. As this is the primary thing of interest to me, I was sort
of surprised that ledger mode offered no pop-up minibuffer to edit the
account, at least from perusing the manual page. I only see options
for reconciling, reports, changing an amount, etc.

In any case, `convert` got most of my stuff into ledger format and
ledger-mode at least recognizes the blocks, so I'll likely just start
from there. I still have *a lot* more reading to do... for example:

- I noticed in the demo file, the co-op (which I snipped above)
purchases were in one chunk vs. treated as separate transactions. I
wouldn't default to this and am guessing it's just a preference thing
(compared to having one transaction per payment)?

- I still wrestle with deposits and withdrawals. Am I the payee? Is my
bank? Does it matter as long as some assets category goes positive and
another negative?

- I'd love tracking checks *as we write them* vs. just waiting for
them to appear. This used to really annoy me in Moneydance, as I'd go
through the checkbook once a month to see what was written but not
come through. Then I'd have to have these little note entries along
the way to remind me what the total of uncashed checks were to-date so
that the sums added up. I bet there was a better way in Moneydance I'd
missed, and I'm positive there's one in ledger/beancount.

Anyway, still taking it slow but feel like I'm starting to get to a
usable noob state.


Thanks,
John

[Michael Norrish]

One way to do two-stage cheques would be something like

2016/1/25 * My Favourite Shop
Expenses:Groceries $100
Liabilities:Unprocessed Checks

2016/1/31 * Check clearing
Liabilities:Unprocessed Checks $100
Assets:Checking Account

You could assuredly add metadata to link the two transactions to be wrt some check #.

Michael

[David Glasser]

One downside to doing it this way is that before you enter the check
clearing transaction, Assets:Checking does not actually answer the question
"how much can I take out of my checking account without bouncing a check",
which surely is a very important use case.

--dave

[Michael Norrish]

A balance command mentioning those two accounts would sum to the amount you want:

   ledger bal assets.checking liabilities:unprocessed

Michael

[David Glasser]

Ah, sure, if there's a 1:1 mapping between unprocessed accounts and
main accounts. (Perhaps Assets:Checking:Unprocessed Checks?)

On Wed, Feb 3, 2016 at 9:32 PM, Michael Norrish

gla...@davidglasser.net | langtonlabs.org | flickr.com/photos/glasser/

[Martin Blais]

On Wed, Feb 3, 2016 at 11:30 PM, Michael Norrish <michael...@gmail.com> wrote:

One way to do two-stage cheques would be something like

2016/1/25 * My Favourite Shop
  Expenses:Groceries   $100
  Liabilities:Unprocessed Checks

2016/1/31 * Check clearing
  Liabilities:Unprocessed Checks  $100
  Assets:Checking Account

You could assuredly add metadata to link the two transactions to be wrt some check #.

Yes, and I'm claiming that this linkage can probably be done by the computer.

 

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/A57D9595-9C57-4D73-9276-86815B04924F%40gmail.com.

[Martin Blais]

This is related to this:

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/plugins/tag_pending.py?at=default&fileviewer=file-view-default

See this thread for context:

https://groups.google.com/d/msg/beancount/z9sPboW4U3c/EQk25vKcHDQJ

[Stefano Zacchiroli]

On Mon, Feb 01, 2016 at 11:41:03PM -0500, Martin Blais wrote:
> Check out LedgerHub for ideas.

[...]

> 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

[...]

> 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.

I've the feeling that, right now, the lack of a generic framework ---
generic both on the front of data source (CSV, OFX, weird bank formats,
etc.) and on that of output formats (ledger, beancount, etc.) --- for
semi-automatically importing transactions is perhaps the most
significant limiting factor for the adoption of CLI accounting.

I went myself through the ad-hoc automation of my work-flow for
importing transactions from my bank, scripting together the web outside
of browsers suite [1] and icsv2ledger. It works decently enough for me,
but the CLI accounting community cannot really expect every newcomer to
go through that hacking process if it wants the community to flourish.

[1]: http://weboob.org/

Martin is right that the most front-end part of the import chain (web
scraping in most cases) will always remain a case-by-case business. But
there, communities like weboob can feel that niche quite nicely, if only
they will manage to grow and be diverse enough. (Right now that
community is very much skewed toward supporting French banks, with very
sparse support for other international banks.)

But the rest of the toolchain, from CSV down to your favorite CLI
accounting tool can really do better in terms of reference tools and
automation. I'm sorry I haven't had time/energy to contribute myself to
ledgerhub, because the design looked pretty solid; I'm looking forward
to the new rewrite :-)

</ramble>
--
Stefano Zacchiroli . . . . . . . za...@upsilon.cc . . . . o . . . o . o
Maître de conférences . . . . . http://upsilon.cc/zack . . . o . . . o o
Former Debian Project Leader . . . . . @zacchiro . . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

[Simon Michael]

On 2/1/16 10:13 AM, John Hendy wrote:
> 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!

Welcome John,

There are quite a lot of CSV(/OFX) to *ledger conversion tools,
including non-interactive ones; I'm not sure if you've found them all
yet. Off the top of my head, there's

- Erik Hetzner's https://gitlab.com/egh/ledger-autosync - converts OFX
not CSV, but many banks provide both. Can also download it from the bank
for you (optional).

- My own http://hledger.org/manual.html#csv - converts CSV to
ledger-compatible journal format with more options, eg you can set the
account(s) based on regexps matching the CSV records.

[Simon Michael]

Hi Stefano, I agree. Many things should be more straightforward and
better documented.

I think we could pick out a few common tasks to focus our
tool-building/documenting efforts on. Eg:

1. importing bank data and CSV generally. All of the tools and basic
generic workflows for this should be described on one page. Focus on
CSV, but we should mention OFX too (ledger-autosync is arguably best at
this with its download feature).

2. exporting all data and reports as CSV

3. moving data between the ledger-likes (ledger, hledger, beancount...).
Again, all tools and techniques gathered on one page. All existing
formats should be listed. The output of "ledger print" is a sort of
lowest common denominator, I propose we give it a name and decree that
every tool should import this as a basic interchange format. And/or a
standardised CSV representation of it, such as "hledger print -O csv"

4. moving data from and to other accounting tools (gnucash, moneydance,
excel, quick{en,books}, mobile account apps)

5. manual data entry. Editors and their modes, ledger entry, hledger add
and other prompting tools, hledger-web, recurring entry scripts, etc.

6. a catalog of journal entries covering all common transactions

[Martin Blais]

On Thu, Feb 4, 2016 at 12:41 PM, Simon Michael <si...@joyful.com> wrote:

Hi Stefano, I agree. Many things should be more straightforward and
better documented.

I think we could pick out a few common tasks to focus our
tool-building/documenting efforts on. Eg:

1. importing bank data and CSV generally. All of the tools and basic
generic workflows for this should be described on one page. Focus on
CSV, but we should mention OFX too (ledger-autosync is arguably best at
this with its download feature).

FWIW, there's a generic OFX converter in LedgerHub as well.

https://bitbucket.org/blais/ledgerhub/src/261b5c29ddc37fd44fe7bba83ad906e41aab47dc/lib/python/ledgerhub/ingest/importers/ofx.py?at=default&fileviewer=file-view-default

(I'm planning to keep that importer in the LedgerHub revamp as a default and example, and will likely include a configurable CSV importer as well.)

 

2. exporting all data and reports as CSV

3. moving data between the ledger-likes (ledger, hledger, beancount...).
Again, all tools and techniques gathered on one page. All existing
formats should be listed. The output of "ledger print" is a sort of
lowest common denominator, I propose we give it a name and decree that
every tool should import this as a basic interchange format. And/or a
standardised CSV representation of it, such as "hledger print -O csv"

Beancount has reports to convert from its syntax to Ledger and HLedger.

Defined here:

https://bitbucket.org/blais/beancount/src/d1d2ef0f1b6faf7bafdadc0f3a6ea20515df5ff5/src/python/beancount/reports/convert_reports.py?at=default&fileviewer=file-view-default

Converting from Beancount to Ledger in a non-lossy way is possible because of the nature of dated assertions, but not possible the other way around, you have to drop the file-based assertions.

[Simon Michael]

We have a lot of docs, in various states of freshness, specific to each
implementation. Also many informative blog and mail list posts. Much of
this is hard to find.

Reading Stefano's recent ledger list post, I think, not for the first
time, wouldn't it be great if we had all of this linked somewhere
central, curated, and presented beautifully, providing an easy on-ramp
and reference for newcomers and experts ? Actively maintained by the
community ?

If so, where would that somewhere be ? ledger-cli.org and/or its wiki is
the closest existing candidate, but it has never felt right to load that
up with non-Ledger stuff. I think it's valuable for each implementation
to have its own distinct site. I think a separate, well-named, highly
findable site, even a single page collecting all useful links and acting
as a portal to the ledgerverse, could be a win.

If you agree, what would you call it ? Martin, since you are retiring
your LedgerHub tool, would that name be available ?


Related to naming.. what do we call this whole topic, anyway ? Stefano
used the phrase "command-line accounting". But we have curses and web
GUIs too. "Plain-text accounting" ? Pretty soon we'll probably support
some non-text storage format. "Ledger clones" ? Too narrow. Aside: in
conversation, I use "ledger-likes" for things similar to but not
necessarily compatible with Ledger (ledger, hledger beancount, abandon,
penny) and "*ledger" for very compatible ledger-likes (ledger, hledger).

Any thoughts ?

-Simon

On 2/4/16 9:41 AM, Simon Michael wrote:
> I think we could pick out a few common tasks to focus our
> tool-building/documenting efforts on. Eg:
>
> 1. importing bank data and CSV generally. All of the tools and basic
> generic workflows for this should be described on one page. Focus on
> CSV, but we should mention OFX too (ledger-autosync is arguably best at
> this with its download feature).
>

> 2. exporting all data and reports as CSV
>
> 3. moving data between the ledger-likes (ledger, hledger, beancount...).
> Again, all tools and techniques gathered on one page. All existing
> formats should be listed. The output of "ledger print" is a sort of
> lowest common denominator, I propose we give it a name and decree that
> every tool should import this as a basic interchange format. And/or a
> standardised CSV representation of it, such as "hledger print -O csv"
>

[John Wiegley]

>>>>> Simon Michael <si...@joyful.com> writes:

> If so, where would that somewhere be ? ledger-cli.org and/or its wiki is the
> closest existing candidate, but it has never felt right to load that up with
> non-Ledger stuff. I think it's valuable for each implementation to have its
> own distinct site. I think a separate, well-named, highly findable site,
> even a single page collecting all useful links and acting as a portal to the
> ledgerverse, could be a win.

I'd be happy for us to move everything Ledger-related to an "All things
Ledger-verse" Wiki, where we differentiate features when necessary among the
various implementations.

> Related to naming.. what do we call this whole topic, anyway ?

How about functional accounting tools?

--
John Wiegley GPG fingerprint = 4710 CF98 AF9B 327B B80F
http://newartisans.com 60E1 46C4 BD1A 7AC1 4BA2

[John Hendy]

On Feb 7, 2016 10:44 AM, "Simon Michael" <si...@joyful.com> wrote:

> If you agree, what would you call it ? Martin, since you are retiring
> your LedgerHub tool, would that name be available ?
>
>
> Related to naming.. what do we call this whole topic, anyway ? Stefano
> used the phrase "command-line accounting". But we have curses and web

For what it's worth, I'm super new and even though I knew what I was looking for... having the tool named the same as a common finance term makes it super tough to find relevant info.

Ever tried googling "ledger tutorial"? :)

In any case, my input would be to call this family of things something that existing users can find, as well as something that might make it on the forest page of Google results (if you'd like new people to find it as well).

John

> Any thoughts ?
>
> -Simon
>
> On 2/4/16 9:41 AM, Simon Michael wrote:
> > I think we could pick out a few common tasks to focus our
> > tool-building/documenting efforts on. Eg:
> >
> > 1. importing bank data and CSV generally. All of the tools and basic
> > generic workflows for this should be described on one page. Focus on
> > CSV, but we should mention OFX too (ledger-autosync is arguably best at
> > this with its download feature).
> >
> > 2. exporting all data and reports as CSV
> >
> > 3. moving data between the ledger-likes (ledger, hledger, beancount...).
> > Again, all tools and techniques gathered on one page. All existing
> > formats should be listed. The output of "ledger print" is a sort of
> > lowest common denominator, I propose we give it a name and decree that
> > every tool should import this as a basic interchange format. And/or a
> > standardised CSV representation of it, such as "hledger print -O csv"
> >
> > 4. moving data from and to other accounting tools (gnucash, moneydance,
> > excel, quick{en,books}, mobile account apps)
> >
> > 5. manual data entry. Editors and their modes, ledger entry, hledger add
> > and other prompting tools, hledger-web, recurring entry scripts, etc.
> >
> > 6. a catalog of journal entries covering all common transactions
> >
>

> --
>
> ---
> You received this message because you are subscribed to a topic in the Google Groups "Ledger" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/ledger-cli/u648SA1o-Ek/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to ledger-cli+...@googlegroups.com.

[Simon Michael]

On 2/7/16 9:32 AM, John Hendy wrote:
> Ever tried googling "ledger tutorial"? :)

Yes (and happily today for me Ledger's manual is the first hit. I might
have got lucky. :)

> In any case, my input would be to call this family of things something that
> existing users can find, as well as something that might make it on the
> forest page of Google results (if you'd like new people to find it as well).

Any suggestions ?

[Craig Earls]

If we really wanted to make it easy to find info we could ask john about a name change. "Ledger" is a pretty common word. We should be all millenial and spell it ledjir or something else english-ish. 

--

---

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 ledger-cli+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[nx]

I always have good luck using "ledger cli" in the search terms.