[feature]: Easy CSV ledger export

[robwoodgate]

It's frustrating trying to reconcile node balance movements for accounting purposes. Right now, multiple queries must be performed via lncli, their JSON transformed and combined. Payments need to have their lightning invoice decoded for description details.

Tools like BoS can help, but getting everything to reconcile from starting balance to end balance is almost impossible, especially when mSats are rounded and BoS is limited by the available APIs (eg to determine owners of channel outputs).

I'd like to see a singlelncli command that can be performed to export a complete CSV of balance affecting transactions.

Something like the ledger format that is typically exported from exchanges / uploaded to crypto tax services:

"Date","Amount","Fee","Currency Unit","Description","Transaction ID","Balance"
"2024-09-09T20:57:04Z","5000000","-511","SATS","0:openchannel:shortchanid-9xxxx","072...34d","4999489"
"2024-09-10T22:33:44Z","-2500000","-1,062","SATS","Kraken Deposit","033...3a2","2498427"
"2024-09-11T10:12:14Z","10000","0","SATS","Paid to Store (Order ID xxx)","4a2...42e","2508427"
"2024-09-12T02:22:22Z","-938234","-494","SATS","0:closechannel:shortchanid-7xxxx","0a2...b3b","1569699"

Options for the report could include:

• reporting currency unit (SATS, mSATS)
• format of data (CSV/JSON)
• start / end dates

This would make adoption by businesses easier by making the accounting easier.

Cheers
Rob

[nordbjorn]

Have out tried Faraday ? It has a nice "node audit" command which can give you good accounting data for your node

[robwoodgate]

I have it as part of the opt-add-lightning-terminal fragment in BTCPay Server, but it doesn't seem to work out of the box.

Not quite sure what I need to get it fixed up, have tried:

docker exec generated-lnd_lit-1 frcli --macaroonpath /data/lnd/admin.macaroon --tlscertpath=/data/lnd/tls.cert audit

Gives a connection error, so assuming something not quite right with the docker setup.

[guggero]

frcli talks to the faraday daemon, not lnd directly. In the case of BTCPay Server, faraday is part of the Lightning Terminal application bundle, running in "remote mode" (meaning it isn't running in the same process as lnd).
Check out this doc, which should help: https://github.com/lightninglabs/lightning-terminal/blob/master/docs/config-lnd-remote.md#example-frcli-command

You might need to adjust the paths to match the BTCPay Server docker setup.

[robwoodgate]

Thanks - with that pointer, I think I have it:

docker exec generated-lnd_lit-1 frcli --rpcserver=localhost:8443 --tlscertpath=/root/.lit/tls.cert --macaroonpath=/root/.faraday/mainnet/faraday.macaroon audit

Will have a play and see what it outputs

[robwoodgate]

hmmm, Faraday requires bitcoin to have the -txindex flag to use --start_time value. It's just a handful of transactions without. I have a pruned node, so can't use this option.

Use -txindex or provide a block hash to enable blockchain transaction queries. Use gettransaction for wallet transactions.

I'm sure lncli could provide this data natively without having to hack about with other tools.

Appreciate your help anyhow, thank you..

[Roasbeef]

hmmm, Faraday requires bitcoin to have the -txindex flag to use

It requires a txindex to make sure it can get all the Bitcoin data needed to generate the accounting report.

[robwoodgate]

The txindex requirement seems to be Faraday specific.

LND supports a pruned node, and I can get the raw data in JSON format using lncli, so it should be possible to implement an audit command in lncli.

Tools like BoS and Faraday are helpful, but accounting is too important a feature to relegate to separate tools.

I would appreciate if this enhancement to lncli could therefore be considered.