Intuitively understand the cost of eating it every day

all know, I use Emacs of ledger-mode to keep accounts (see the previous article "Programmer's Accounting Tools-Ledger and Ledger-mode" ). As a good command-line reporting tool, ledger command balance and register enough to cover the majority of usage scenarios:

• balance can generate a statement of the balance of all accounts, which is used to compare with the real balance of each account every day;
• register can generate transaction details of a given account, which can be used to check the flow of the real account one by one when the balance is inconsistent;

The fly in the ointment is that ledger are not intuitive enough because they are icy textual information, not exciting statistical graphics. Fortunately, just as ledger does not store data, but is just a porter of transaction records in the .ledger gnuplot is also such a tool-it does not store data, it is only responsible for the data stored in text files as graphics The form appears.

How to use gnuplot

gnuplot is very easy to use. Take the simplest case as an example, first save the following content to the file /tmp/data.csv

-1 -1
0 0
1 1

gnuplot in the command line, enter its REPL, and execute the following commands

plot "/tmp/data.csv"

You can get the display of these three sets of data

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

Therefore, if you want gnuplot draw an overhead graph, first extract the data to be drawn from the gnuplot , and then decide how to draw with 0615db9fd99e02.

Use ledger extract overhead records

Although the ledger of register can print out the transaction details of a given account, it is more suitable to use the csv here. For example, the following command can print out the earliest 10 expenditure records of food 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:4c8f1ee7:5eeaddf3:f879914e","A","10.5","",""
"2019/09/12","","139b790f","a137e4ee:9bc8ee49:7d7ccd8b:472d6007","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","",""

--anon option can anonymize the sensitive information (such as the payee, account number) in the transaction details.

Although ledger has 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 eating expenses in a day, there will be multiple transactions on the same day. Before drawing, you need to add up the expenses on the same day, leaving only one number. Both of these requirements can be met csvsql

Aggregate data with csvsql

Take the 10 records in the previous article as an example, use the following command to aggregate them by day

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'

in:

• The option -H lets csvsql know that the data entered from the pipeline does not have a header row. In subsequent processing, csvsql will use a , b , c etc. as column names by default;
• Option --query used to submit the SQL statement to be executed;
• Option --tables used to specify the name of the table, so that it can be processed by SQL --query

The result is as follows

➜  Accounting 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'
a,SUM(`f`)
2019-09-10,20
2019-09-11,40
2019-09-12,182.4
2019-09-13,63

Use gnuplot read data and plot

Use redirection to csvsql the output results of /tmp/data.csv to the file 0615db9fd99f98, and then you can use gnuplot to draw them

➜  Accounting 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' | tail -n '+2' > /tmp/data.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 title '吃的开销'
set output '/tmp/xyz.png'
set timefmt '%Y-%m-%d'
set xdata time
set xlabel '日期'
set xrange ['2019-09-10':'2019-09-13']
set ylabel '金额(¥)'
set yrange [0:200]
set datafile separator comma
plot '/tmp/data.csv' using 1:2
➜  Accounting gnuplot /tmp/plot_expense.gplot

The generated picture file /tmp/xyz.png as follows

The commands used in the script file /tmp/plot_expense.gplot gnuplot in the online manual :

• set format command is used to set the format of the scale of the coordinate axis. set format x "%y-%m-%d" means to set the scale of the X axis to the format of 19-09-10
• set style data command sets the drawing style of the data. set style data box indicates the use of a hollow histogram;
• set terminal command is used to tell gnuplot what kind of output should be generated. set terminal png font '/System/Library/Fonts/Hiragino Sans GB.ttc' indicates that the output result is a picture in PNG format, and the given font is used;
• set title command controls the title copy at the top middle of the output result;
• set output command is used to redirect the content originally output on the screen to a file;
• set timefmt command is used to specify the format of the input date and time data. set timefmt '%Y-%m-%d' means that the input date and time data is in a format 2019-09-10
• set xdata command controls gnuplot understands the data belonging to the X axis. set xdata time indicates that all data on the X axis are time-type data;
• set xlabel command to control the text of the meaning of the X axis. set ylabel is similar to it, except that it acts on the Y axis;
• set xrange command controls the display range on the X axis in the graph drawn by gnuplot
• set datafile separator command control gnuplot reading the data file separator between the lines, comma expressed separator is a comma.

What should I do if I want to keep statistics on a weekly basis

Suppose I want to check the total expenditure on food in each week of 2021, then I need to csvsql the data in 0615db9fd9a188 according to the week in which it is located

➜  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_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
09,375.73

Also need to adjust the gnuplot script

set terminal png font '/System/Library/Fonts/Hiragino Sans GB.ttc'
set title '吃的开销'
set output '/tmp/xyz2.png'
set xlabel '第几周'
set xrange [0:54]
set ylabel '金额(¥)'
set yrange [0:1000]
set datafile separator comma
plot '/tmp/expense_dow.csv' using 1:2 with lines

The result is as follows

What should I do if I want to view two years of graphics at the same time

gnuplot supports drawing multiple curves at the same time, as long as you use different columns in the data file as the ordinate. Suppose I want to compare 2020 and 2021, then first calculate the expenditures for the two years into different documents

➜  Accounting ledger -b '2020-01-01' -e '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_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.csv

Combine the data in the same week

➜  Accounting csvjoin -H -c a /tmp/expense_2020.csv /tmp/expense_2021.csv | tail -n '+2' > /tmp/expense_2years.csv

Finally, let gnuplot draw two polylines at once

set terminal png font '/System/Library/Fonts/Hiragino Sans GB.ttc'
set title '吃的开销'
set output '/tmp/xyz2years.png'
set xlabel '第几周'
set xrange [0:54]
set ylabel '金额(¥)'
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"

The result is as follows

postscript

In fact, it is still very unintuitive, because what is finally generated is a static picture, and it can't achieve the effect of giving the ordinate of the position when the mouse is moved to the curve.

Read the original text