As we all know, I use Emacs’ ledger-mode for bookkeeping (see previous article, Ledger and ledger-mode for Programmers). Ledger’s commands balance and Register, an excellent command-line reporting tool, cover most usage scenarios:

balanceCan generate balance reports of all accounts for daily comparison with the real balance of each account;
registerCan generate the transaction details of the given account, used in the balance is inconsistent with the flow of the real account line by line check;

Ledger reports, on the other hand, are less intuitive, because they are cold text messages rather than uplifting statistical graphs. Fortunately, just as Ledger does not store data, but is a porter of transactions in a ledger file, gnuplot is one of those tools — it does not store data, but only graphically presents the data stored in a text file.

How to use`gnuplot`

Gnuplot is easy to use. In the simplest case, save the following contents to the file/TMP /data.csv

-1-1 0 0 1 1Copy the code

Then start gnuplot on the command line, go into its REPL, and execute the following command

plot "/tmp/data.csv"
Copy the code

You get a presentation of these three sets of data

The three sets of data are the points with coordinates of (-1, -1), (0, 0) and (1, 1) respectively.

So for Gnuplot to plot overhead, the first step is to extract the data to plot from the ledger and then decide how to plot it using Gnuplot.

with`ledger`Extract cost record

Although ledger subcommand register can print transaction details for a given account, CSV subcommands are more appropriate here. For example, the following command can print the first 10 spending records for food items in CSV format

➜ Accounting ledger --anon --head 10-f 2021.ledger CSV 'Expense:Food' "2019/09/10","","32034acc","efe2a5b9:c720f278:58a3cd91:0dc07b7b","A","20","","" "2019/09/11","","a61b6164","5d45e249:fe84ca06:778d1855:daf61ede","A","5","","" "2019/09/11","","674ec19f","5d018df1:ebf020db:29d43aba:d0c84127","A","15","","" "2019/09/11","","e55ff018","370ca545:7d3aa2d0:86f5f330:1379261b","A","20","","" "2019/09/12", ""," f6aa675c ", "08315491:4 c8f1ee7:5 eeaddf3: f879914e", "A", "10.5", ""," " "2019/09/12", ""," 139 b790f ", "a137e4ee: 9 bc8ee49:7 d7ccd8b: 472 d6007", "A", "23.9", ""," " "2019/09/12","","b24b716d","de348971:5364622c:b2144d94:01e74ff3","A","148","","" "2019/09/13","","e7c066fa","b418a3b2:a3e21e87:a32ee8ac:8716a847","A","3","","" "2019/09/13","","9eb044fe","702a13e9:3de7f1bd:9b20a278:1d20668d","A","24","","" "2019/09/13","","ba301270","d2b7eeb3:381f9473:54f86a33:391a8662","A","36","",""Copy the code

— The Anon option can anonymize sensitive information in transaction details (such as payee, account number).

Although the ledger prints many columns, only the date in the first column and the amount in the sixth column are what I need. At the same time, since there may be multiple food expenditures in a day, there may also be multiple transactions in the same day. Before plotting, you need to add up the expenditures in the same day, leaving only one number. Both of these requirements can be met with CSVSQL.

with`csvsql`Aggregate data

Taking the 10 records from the previous article as an example, you can aggregate them by day with the following command

ledger --anon --head 10 -f 2021.ledger csv 'Expense:Food' | csvsql -H --query 'SELECT `a`, SUM(`f`) FROM `expense` GROUP BY `a` ORDER BY `a` ASC' --tables 'expense'
Copy the code

Among them:

options-HletcsvsqlKnow that the data entered from the pipe does not have a header line. And when I do that,csvsqlWill default toa,b,cEtc as column names;
options--queryTo submit the SQL statement to execute;
options--tablesUsed to specify the name of the table so that--queryTo use SQL to process it;

The results are as follows

➜ Accounting gotten - anon - head - f 2021. 10 gotten CSV 'Expense: Food | CSVSQL - H - query' SELECT ` a `, SUM(' f ') FROM 'expense' GROUP BY 'a' ORDER BY 'a' ASC' --tables 'expense' a,SUM(' f ') 2019-09-10,20 2019-09-11,40 2019-09-12, 182.4 2019-09-13, 63Copy the code

with`gnuplot`Read data and plot

Use redirection to save the CSVSQL output to the file/TMP /data.csv, which can then be plotted out using gnuplot

➜ Accounting gotten - anon - head - f 2021. 10 gotten CSV 'Expense: Food | CSVSQL - H - query' SELECT ` a `, The SUM (` f `) FROM ` expense ` GROUP BY ` a ` ORDER BY ` a ` ASC '- tables' expense' | tail - n '+ 2' > / TMP/data. The CSV ➜ Accounting cat /tmp/plot_expense.gplot set format x '%y-%m-%d' set style data boxes set terminal png font '/ System/Library/Fonts/Hiragino Sans GB TTC' set the title 'eat the cost of the set output'/TMP/xyz. PNG 'set timefmt' % Y - % m - % d 'set xdata Time set xlabel 'date' set xrange ['2019-09-10':'2019-09-13'] set ylabel 'value (¥)' set yrange [0:200] set datafile Separator comma plot '/ TMP /data.csv' using 1:2 ➜ Accounting gnuplot/TMP /plot_expense.gplotCopy the code

The generated image file/TMP /xyz.png is as follows

The commands used in the/TMP /plot_expense. Gplot script are available in the online manual of gnuplot:

set formatThe command is used to format the scale of the coordinate axis.set format x "%y-%m-%d"This means to set the X-axis scale to something likeThe 19-09-10The format of the;
set style dataCommand to set the drawing style of data.set style data boxRepresents the hollow bar chart;
set terminalCommands are used to tellgnuplotWhat output should be generated.set terminal png font '/System/Library/Fonts/Hiragino Sans GB.ttc'Represents a PNG image output with the given font;
set titleCommand to control the headline copywriting at the top center of the output;
set outputThe redirection command is used to redirect the output to a file.
set timefmtThe date and time command is used to specify the format of the entered date and time data.set timefmt '%Y-%m-%d'Means that the input date and time data looks like2019-09-10The format of the;
set xdataCommand and controlgnuplotHow to make sense of data belonging to the X-axis.set xdata timeRepresents all time-type data on X-axis;
set xlabelCommand controls the meaning of the X-axis copywriting.set ylabelIt’s similar, but it’s acting on the Y-axis;
set xrangeCommand and controlgnuplotThe display range on the X-axis of the graph drawn;
set datafile separatorCommand and controlgnuplotThe separator between columns when reading a data file,commaIndicates that the separator is a comma.

What if you want to do weekly statistics

Suppose I want to look at the total amount spent on food per week in 2021, then I need to aggregate the data by week in CSVSQL

➜ Accounting gotten - b 'the 2021-01-01 - f 2021. Gotten CSV' Expense: Food | CSVSQL - H - query 'SELECT strftime (" % W ", `a`) AS `week`, The SUM (` f `) FROM ` expense ` GROUP BY ` week ` ORDER BY ` a ` ASC '- tables' expense' | tail - n '+ 2' > / TMP/expense_dow CSV ➜ Accounting Head/TMP /expense_dow.csv 00,633.6 01,437.3 02,337.5 03,428.4 04,191.5 05,330.4 06,154.6 07,621.4 08,485.6 09375.73Copy the code

You also need to adjust the gnuplot script

Set terminal PNG font '/ System/Library/Fonts/Hiragino Sans GB TTC' set the title 'eat the cost of the set output'/TMP/xyz2. PNG 'set Xlabel 'week' set xrange [0:54] set ylabel 'amount (¥)' set yrange [0:1000] set datafile separator comma plot '/tmp/expense_dow.csv' using 1:2 with linesCopy the code

The results are as follows

What if you want to see two years of graphics at the same time

Gnuplot supports plotting multiple curves at the same time, as long as different columns in the data file are used as ordinates. Let’s say I want to compare 2020 and 2021, then I’ll put the expenses for each of the two years into different files

➜ Accounting gotten - b '2020-01-01 -' 2021-01-01 'e - f 2021. Gotten CSV' Expense: Food | CSVSQL - H - query 'SELECT strftime("%W", `a`) AS `week`, The SUM (` f `) FROM ` expense ` GROUP BY ` week ` ORDER BY ` a ` ASC '- tables' expense' | tail - n '+ 2' > / TMP/expense_2020 CSV ➜ Accounting ledger -b '2021-01-01' -f 2021.ledger csv 'Expense:Food' | csvsql -H --query 'SELECT strftime("%W", `a`) AS `week`, SUM(`f`) FROM `expense` GROUP BY `week` ORDER BY `a` ASC' --tables 'expense' | tail -n '+2' > /tmp/expense_2021.csvCopy the code

The data are then combined for the same week

➜ Accounting csvjoin - H - a/TMP/c expense_2020. CSV/TMP/expense_2021 CSV | tail -n '+ 2 > / TMP/expense_2years CSVCopy the code

Finally, let Gnuplot draw two more polylines at once

Set terminal PNG font '/ System/Library/Fonts/Hiragino Sans GB TTC' set the title 'eat the cost of the set output'/TMP/xyz2years. PNG 'set Xlabel 'week' set xrange [0:54] set ylabel 'amount (¥)' set yrange [0:1000] set datafile separator comma plot '/tmp/expense_2years.csv' using 1:2 with lines title "2020", '/tmp/expense_2years.csv' using 1:3 with lines title "2021"Copy the code

The results are as follows

Afterword.

It’s still very unintuitive, because the result is a static image that doesn’t give you the ordinate of your position when you hover your mouse over a curve.

Read the original

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Visualize how much you spend on food each day

How to use`gnuplot`

with`ledger`Extract cost record

with`csvsql`Aggregate data

with`gnuplot`Read data and plot

What if you want to do weekly statistics

What if you want to see two years of graphics at the same time

Afterword.

Visualize how much you spend on food each day

How to usegnuplot

withledgerExtract cost record

withcsvsqlAggregate data

withgnuplotRead data and plot

What if you want to do weekly statistics

What if you want to see two years of graphics at the same time

Afterword.

Related Posts

R language uses the optimal cluster number k-medoids clustering for customer segmentation

Jenkins deploys/builds configuration/switches themes

Performance optimization | This is probably the most well-summarized article on the technical essentials of the garbage collector

How to use`gnuplot`

with`ledger`Extract cost record

with`csvsql`Aggregate data

with`gnuplot`Read data and plot