If you seriously decide to take into account personal finances, then one of the first difficulties for you is likely to be data collection. In the comments to the previous article , where I unsuccessfully tried to justify the very need for money management, people with the highest level of self-discipline were noted, for whom this question is not a question. They are disciplined day after day to write their spending on a mobile phone over the years. Personally, I can say for myself that I have never succeeded, and to be honest with myself, I probably will never begin to succeed in the future. Therefore, this path is clearly not for me. But I found a solution!

To my luck, my phone already registers all my expenses without my participation. For each operation on my card I receive an SMS notification from the bank, in which most of the necessary information is contained:

• Amount of payment
• Merchant Terminal Code
• Time of payment
• Balance
• Charge Card ID

Thus, to obtain primary data for analysis, we need to unload SMS messages. I am a geek and therefore I have a rooted Android phone. In my case, this task is trivial:

$ adb shell "su -c 'cp /data/data/com.android.providers.telephony/databases/mmssms.db /mnt/sdcard'" $ adb pull /mnt/sdcard/mmssms.db > /dev/null $ adb shell "rm /mnt/sdcard/mmssms.db" 

(adb [android debug bridge] program included in the Android SDK. There are implementations for both Windows and Linux / Mac. To access the mmssms.db database, you need root privileges. If anyone knows how to get this file without root, please write in the comments)

As a result of the script, we have an ordinary SQLite3 database. You can work with it with the help of the sqlite package, the implementations of which are also available in Linux, Windows and Mac. In this database, we are interested in the SMS table, which contains the columns address and body .

 $ sqlite3 mmssms.db SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. sqlite> PRAGMA table_info(sms); 0|_id|INTEGER|0||1 1|thread_id|INTEGER|0||0 2|address|TEXT|0||0 3|m_size|INTEGER|0||0 4|person|INTEGER|0||0 5|date|INTEGER|0||0 6|date_sent|INTEGER|0|0|0 7|protocol|INTEGER|0||0 8|read|INTEGER|0|0|0 9|status|INTEGER|0|-1|0 10|type|INTEGER|0||0 11|reply_path_present|INTEGER|0||0 12|subject|TEXT|0||0 13|body|TEXT|0||0 14|service_center|TEXT|0||0 15|locked|INTEGER|0|0|0 16|sim_id|INTEGER|0|-1|0 17|error_code|INTEGER|0|0|0 18|seen|INTEGER|0|0|0 19|ipmsg_id|INTEGER|0|0|0 

With the help of a usual sql query, we can select SMS messages sent by Sberbank:

  $ echo "select trim(body, X'0A') from sms where address = '900' order by date asc" | sqlite3 mmssms.db 

Here 900 is the number from which Sberbank sends notifications. The trim function removes the carriage transfer at the end of the message, which for some reason, put some banks. Here is an example of the output:

 VISA2222 01.01.18 08:43  1245 GAZPROMNEFT : 1985.29 VISA2222 01.01.18 12:05  176.50 YARCHE : 1808.79 VISA2222 01.01.18 12:16  504 FRUKTY : 1304.79 VISA2222 01.01.18 15:09  441 KFC : 863.79 ECMC1111 01.01.18 17:52  15.09EUR HOSTING COMPANY : 66679.05 ECMC1111 02.01.18 19:41  104 MCDONALDS : 66583.47 ECMC1111 03.01.18 08:49  205.10 MARIYA-RA : 66378.37 ECMC1111 03.01.18 09:16  810 FIT SERVICE : 65568.37 VISA2222 03.01.18 09:17  220 EKSKLYUZIV : 643.79 ECMC1111 03.01.18 09:18  4200 FIT SERVICE : 61368.37 

My spouse and I use different cards. You can differentiate the payer by card ID. By the terminal code you can find out exactly what the money and the spent amount were spent on. In principle, this is quite enough to get some statistical picture.

Data processing

Starting from this point, then everyone can go their own way. Someone can write a Python script for parsing and analyzing. Someone can use AWK to overtake data in CSV and analyze in Excel. I use ledger cli for analysis. On the topic of ledger on Habré there is only one article "Financial accounting in the text console" by estet . If you can not wait to start, I recommend to read this article.

In the next article, I will tell you how I transfer data from SMS messages to text format Ledger, as well as how I organized this file.