Re: Getting started; assigning accounts to bank .csv data

[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


On Tue, Feb 2, 2016 at 11:07 PM, Martin Blais  wrote:

> On Tue, Feb 2, 2016 at 10:48 PM, John Hendy  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  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.

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

Re: Getting started; assigning accounts to bank .csv data

[John Hendy]

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

Re: Getting started; assigning accounts to bank .csv data

[Martin Blais]

On Tue, Feb 2, 2016 at 10:48 PM, John Hendy  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  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.
>>>
>>
>>
> 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.
>

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