Management for the geek: where to get the data?

  • Tutorial

image


If you have seriously decided to take into account personal finances, then one of the first difficulties for you will probably 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 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 phone on Android. 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.02018-01-2218:45:57
Enter ".help"for usage hints.
sqlite> PRAGMA table_info(sms);
0|_id|INTEGER|0||1
1|thread_id|INTEGER|0||02|address|TEXT|0||0
3|m_size|INTEGER|0||04|person|INTEGER|0||0
5|date|INTEGER|0||06|date_sent|INTEGER|0|0|0
7|protocol|INTEGER|0||08|read|INTEGER|0|0|0
9|status|INTEGER|0|-1|010|type|INTEGER|0||0
11|reply_path_present|INTEGER|0||012|subject|TEXT|0||0
13|body|TEXT|0||014|service_center|TEXT|0||0
15|locked|INTEGER|0|0|016|sim_id|INTEGER|0|-1|0
17|error_code|INTEGER|0|0|018|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 "selecttrim(body, X'0A') from sms where address = '900'orderbydateasc" | sqlite3 mmssms.db

Here 900 is the number from which Sberbank sends notifications. The trim function removes the carriage return 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.09EURHOSTINGCOMPANY Баланс: 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р FITSERVICE Баланс: 65568.37р
VISA2222 03.01.18 09:17 покупка 220р EKSKLYUZIV Баланс: 643.79р
ECMC1111 03.01.18 09:18 покупка 4200р FITSERVICE Баланс: 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.


Also popular now: