Re: ledger-autosync: CSV support

[Erik Hetzner]

Hi Martin,

On Sat, 06 Aug 2016 21:16:40 -0700,
Martin Blais  wrote:
>
>
> Storing a checksum for the imported row suffers from the problem that if
> the user does not immediately copy the result of our conversion, it will
> not be imported further, it could get lost.
>
> Beancount cross-checks extracted transactions against the contents of its
> destination ledger, but because the user often massages the transactions it
> has to use heuristics in order to perform an approximate match to determine
> which transactions have already been seen. The heuristic I have in place
> doesn't work too well at the moment (but it could be improved easily to be
> honest).
>
> A better idea would be to store a unique tag computed from the checksum of
> the input row and to cross-check the imported transactions against that
> special tag. That uses both your insight around validating the input
> instead of the resulting transaction, and uses the ledger instead of a
> temporary cache. It's the best of both worlds.

Thanks for the comment. I’m not sure the distinction that you are making here.
What I do, and I admit I only thought it through for a few minutes as I don’t
actually use Mint but just wanted a simple CSV format for examples - is:

1. Take the input key-value pairs for the row, e.g. Date=2016/01/10
2. Sort by key
3. Generate a string from the key-value pairs and calculate the MD5 checksum
4. Check against a metadata value in ledger using the checksum,
   a. If the row has already been imported, do nothing
   b. If the row is new (no match), import it.

Here is an example of a generated ledger transaction:

2016/08/02 Amazon
; csvid: mint.a7c028a73d76956453dab634e8e5bdc1
1234  $29.99
Expenses:Shopping-$29.99

As you can see, the csvid metadata field is what we query against using ledger
to see if the transaction is already present.

> Simlarly, Beancount has a powerful but admittedly immature CSV importer
> growing:
> https://bitbucket.org/blais/beancount/src/9f3377eb58fe9ec8cfea8d9e3d56f2446d05592f/src/python/beancount/ingest/importers/csv.py
>
> I've switched to using this and CSV file formats whenever I have them
> available - banking, credit cards, 401k.
>
> I'd like to make a routine to try to auto-detect the columns eventually, at
> the moment, they must be configured when creating the importer
> configuration.

Thanks for the pointer - it does look a lot more flexible than my
implementation.

I decided it what simpler, for my needs, to require a new class for each type of
CSV file. It was too much trouble to try to make it configurable. The core code
handles reading the CSV file, deduplicating, and all of that. The CSV class
simply implements a `convert(row)` method which returns a `Transaction` data
structure. I hope that if others need to import a particular type of CSV file,
e.g. from their bank, they can contribute that back to the project.

best, Erik
--
Sent from my free software system .

-- 

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

Re: ledger-autosync: CSV support

[Martin Blais]

On Wed, Aug 3, 2016 at 1:44 AM, Erik Hetzner  wrote:

> Hi all,
>
> I have added some basic CSV support to ledger-autosync. It has not been
> released
> on pypi, but is available currently via source on gitlab and github.
>
> The features works much like the existing OFX file support in
> ledger-autosync.
> ledger-autosync is invoked with a command line argument of a file path, and
> ledger-autosync prints a series of ledger transactions to stdout.
>
> The advantage ledger-autosync has over other CSV to ledger converters is
> the
> deduplication features. For CSV files which include a unique ID per row,
> the
> transactions will be deduplicated similarly to the existing feature for OFX
> files. For CSV formats that do not include a unique per row, an MD5 sum
> will be
> generated from the row content which will be used to deduplicate.
>

Storing a checksum for the imported row suffers from the problem that if
the user does not immediately copy the result of our conversion, it will
not be imported further, it could get lost.

Beancount cross-checks extracted transactions against the contents of its
destination ledger, but because the user often massages the transactions it
has to use heuristics in order to perform an approximate match to determine
which transactions have already been seen. The heuristic I have in place
doesn't work too well at the moment (but it could be improved easily to be
honest).

A better idea would be to store a unique tag computed from the checksum of
the input row and to cross-check the imported transactions against that
special tag. That uses both your insight around validating the input
instead of the resulting transaction, and uses the ledger instead of a
temporary cache. It's the best of both worlds.





>
> For the moment, I have only added support for Paypal, Amazon and Mint CSV
> formats, as this is all I had available. However, it should be easy enough
> for
> developers to add support for new CSV formats. Examples of converters can
> be found here:
>
>   https://gitlab.com/egh/ledger-autosync/blob/master/
> ledgerautosync/converter.py#L316
>
> I hope that this proves helpful! Feedback welcome. The source is available
> at
> gitlab and github:
>
>   https://{gitlab,github}.com/egh/ledger-autosync
>
> I have also recently been working on the 401k and investment features of
> ledger-autosync, which are not pretty robust, if largely undocumented.
> Anyone
> who is not currently tracking their 401k via ledger is encouraged to give
> it a
> try!
>

Simlarly, Beancount has a powerful but admittedly immature CSV importer
growing:
https://bitbucket.org/blais/beancount/src/9f3377eb58fe9ec8cfea8d9e3d56f2446d05592f/src/python/beancount/ingest/importers/csv.py

I've switched to using this and CSV file formats whenever I have them
available - banking, credit cards, 401k.

I'd like to make a routine to try to auto-detect the columns eventually, at
the moment, they must be configured when creating the importer
configuration.

-- 

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

Re: Trial balance from ledger

[Vikas Rawal]

>> 
>> On 8/5/16 10:31 PM, Vikas Rawal wrote:
>>> My auditor, who does not use ledger, wants me to give him trial balances. 
>>> How can I create trial balance from ledger?
>> 
>> Going by https://en.wikipedia.org/wiki/Trial_balance , I think this is a 
>> report showing that all credits and debits to all accounts (assets, 
>> liabilities, revenues, expenses.. and equity ? guess so) add up to 0.
>> 
>> "ledger balance" should show a 0 grand total. Is that a trial balance report 
>> ? I always feel it's not quite the same since we don't separate credits and 
>> debits as strictly.
>> 
>> I'd like to figure it out once and for all. Do we lose information by using 
>> signed posting amounts, compared to traditional debit and credit practice ? 
>> Or are they literally just different notations ?
>> 
>> And, there's this: "ledger balance --dc".
>> 
>> 
> 


How do I format this output. I would like something like (to turn this into an 
orgmode table):

--format "|%(account) |%(amount) |%(amount) |%(quantity(total_expr(amount)))|\n”

But what do I use in place of %(amount) when I use —dc?

Vikas

-- 

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

Re: Trial balance from ledger

[Vikas Rawal]

> 
> On 8/5/16 10:31 PM, Vikas Rawal wrote:
>> My auditor, who does not use ledger, wants me to give him trial balances. 
>> How can I create trial balance from ledger?
> 
> Going by https://en.wikipedia.org/wiki/Trial_balance , I think this is a 
> report showing that all credits and debits to all accounts (assets, 
> liabilities, revenues, expenses.. and equity ? guess so) add up to 0.
> 
> "ledger balance" should show a 0 grand total. Is that a trial balance report 
> ? I always feel it's not quite the same since we don't separate credits and 
> debits as strictly.
> 
> I'd like to figure it out once and for all. Do we lose information by using 
> signed posting amounts, compared to traditional debit and credit practice ? 
> Or are they literally just different notations ?
> 
> And, there's this: "ledger balance --dc".
> 
> 

This is it!

Thanks. For me, signed amounts bring sanity to accounting. So, thumbs up for 
that.

Vikas

-- 

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

Re: Trial balance from ledger

[Martin Blais]

On Sat, Aug 6, 2016 at 8:20 PM, Simon Michael  wrote:

> On 8/5/16 10:31 PM, Vikas Rawal wrote:
>
>> My auditor, who does not use ledger, wants me to give him trial balances.
>> How can I create trial balance from ledger?
>>
>
> Going by https://en.wikipedia.org/wiki/Trial_balance , I think this is a
> report showing that all credits and debits to all accounts (assets,
> liabilities, revenues, expenses.. and equity ? guess so) add up to 0.
>
> "ledger balance" should show a 0 grand total. Is that a trial balance
> report ? I always feel it's not quite the same since we don't separate
> credits and debits as strictly.
>
> I'd like to figure it out once and for all. Do we lose information by
> using signed posting amounts, compared to traditional debit and credit
> practice ?


No.



> Or are they literally just different notations ?
>

Yes.

However, there are two sources of errors:
- Rounding errors on transactions due to acceptable tolerance (cli
accounting would be impossible without that).
- The treatment of currency conversions.
Beancount deals with both of these elegantly.

-- 

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

Re: Trial balance from ledger

[Simon Michael]

On 8/5/16 10:31 PM, Vikas Rawal wrote:

My auditor, who does not use ledger, wants me to give him trial balances. How 
can I create trial balance from ledger?


Going by https://en.wikipedia.org/wiki/Trial_balance , I think this is a 
report showing that all credits and debits to all accounts (assets, 
liabilities, revenues, expenses.. and equity ? guess so) add up to 0.


"ledger balance" should show a 0 grand total. Is that a trial balance 
report ? I always feel it's not quite the same since we don't separate 
credits and debits as strictly.


I'd like to figure it out once and for all. Do we lose information by 
using signed posting amounts, compared to traditional debit and credit 
practice ? Or are they literally just different notations ?


And, there's this: "ledger balance --dc".



--

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

Re: Emcas: Trying to complete an entry via C-TAB doesn't seem to work

[Craig Earls]

You are supposed to put the date of the transaction in first, then the
partial payee name.  Then it works fine.

I will look into improving the error response.

On Sat, Aug 6, 2016 at 1:53 AM, Kaito Michishige  wrote:

> Hi!
>
> I was looking at using xact to make adding entries easier, and found that
> (ledger-fully-complete-xact) is bound to C-TAB by default. However, trying
> in a few letters from an existing payee and hitting C-TAB results in the
> following error message:
>
> ledger-trim-trailing-whitespace: Wrong type argument: arrayp, nil
>
> Arch Linux
> Emacs 24.5
> Ledger mode 20160716.1259 from Melpa
>
> --
>
> ---
> 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+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Craig, Corona De Tucson, AZ
enderw88.wordpress.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 ledger-cli+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Emcas: Trying to complete an entry via C-TAB doesn't seem to work

[Kaito Michishige]

Hi!

I was looking at using xact to make adding entries easier, and found that 
(ledger-fully-complete-xact) is bound to C-TAB by default. However, trying 
in a few letters from an existing payee and hitting C-TAB results in the 
following error message:

ledger-trim-trailing-whitespace: Wrong type argument: arrayp, nil

Arch Linux
Emacs 24.5
Ledger mode 20160716.1259 from Melpa

-- 

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