Using R as an analysis and reporting tool

[Richard Gott]

Hello all

I have trouble with the standard balance sheet output.  It's OK for me but my committee is completely thrown by minus signs etc.  I have tried the various ledger add-ons with no success (running Arch Linux (Manjaro) and Ubuntu 18).

So I decided to send all the data to R (https://cran.r-project.org/) which is very stable, well suppoorted and flexible.

I doubt if this is of much interest but, just in case, here goes:

This is the R syntax for reading a balance sheet adn the raw ledger data.  The syntax shown will read the latest version of the data so it is always up to date.

Balance sheet:

------------------

# Read ledger balance and create temp text file, check that its ok -----------------------
     system("ledger bal --flat --depth 4 > depth4.txt")
     system("tail depth4.txt ")

# set nrows to miss out total line at bottom, add column names
     bal <- read.table("depth4.txt", sep="", header=F, nrows=54)
     colnames(bal) <- c("Amount","Account")
    
# remove £ adn force to numeric
     bal$Amount <- as.numeric(gsub("£", "", bal$Amount))
     str(bal)

# Income adn expenses separated out
          Bal_income <- subset(bal, grepl("Income",Account))
          Bal_expenses <- subset(bal, grepl("Expenses",Account))

# swap columns adn remove minus sign - Income only shown
          Bal_income <- Bal_income[,c('Account', 'Amount')]
          Bal_income$Amount <- - Bal_income$Amount
          head(Bal_income)

---------------------

                                  Account  Amount
34                   Income:Bank_interest    2.33
35     Income:Concert_income:Other:Folder   20.00
36 Income:Concert_income:Other:Music_hire  701.50
37 Income:Concert_income:Other:Programmes  214.95
38  Income:Concert_income:Tickets:Advance 1068.00
39     Income:Concert_income:Tickets:Door  280.00

(this will align OK onteh screen and in a spreadheet)

I then send this directly to an excel sheet using the R package 'xlsx'.  jhis allows you to create a sheet, and send the results to a specific cell start position.  Cut and paste is ok as well.

The ledger raw data:

----------------------------------------

# load lubridate package to handle date format

library("lubridate")

# Read ledger files and create csv

system("echo Date,Code,Payee,Account,Currency,Amount,Status,Notes  >  ledger.csv")
system("ledger main.ledger csv  -S date >> ledger.csv")

# Load into R
     ledger <- read.csv("ledger.csv", header=T)
# convert date format to ISO (optional)

# convert others to more sensible format

    ledger$Date <- ymd(ledger$Date)
     ledger$Date <- as.Date(ledger$Date)
     ledger$Code <- as.character(ledger$Code)
     ledger$Payee <- as.character(ledger$Payee)
     ledger$Notes <- as.character(ledger$Notes)

------------------

You can now run, for eg, a simple bit of syntax to produce a graph of balance for the year, or income , or whatever.  Plenty of other options avaialbe for pie charts etc.

If you have been, thanks for reading.

Richard

[psionl0]

On Sunday, August 26, 2018 at 3:57:30 AM UTC+8, Richard Gott wrote:

Hello all

I have trouble with the standard balance sheet output.  It's OK for me but my committee is completely thrown by minus signs etc. 

And they understand "debit/credit" better?

[Richard Gott]

Hi again

DON'T DO IT!!!

--flat gives entirely unpredictable results.  Thought I had solved it but, depending on what level you use in --depth you can  get gibberish.

Genuine bug here.

--

---
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/RTS3Qw2rZFc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Martin Michlmayr]

* Richard Gott <richard....@gmail.com> [2018-08-26 12:08]:

> --flat gives entirely unpredictable results.

They are afaict very predictable.

> Thought I had solved it but, depending on what level you use in
> --depth you can get gibberish.

But yes, combining --flat with --depth is currently not a good idea.

--
Martin Michlmayr
https://www.cyrius.com/

[Klauss Hass]

Would it be possible to use this script with the prices.db file and get investments market value over time?

--

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

[richard gott]

Sorry. You are quite right. I just failed to predict them.

Ledger is still great!

--

---
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/RTS3Qw2rZFc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ledger-cli+...@googlegroups.com.

[richard gott]

In principle yes. I'll have a go and post my inadequacies.

Richard

--

---

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.