Small automation with Telegram Bot for price analytics

Dmytro Filipenko — Wed, 22 Jul 2020 02:06:46 +0000

How much time did you deal with a situation when realtors are trying to cheat on you? When do they announce prices, which is much higher than it should be?

Right now(July 2020), I'm in position when with my spouse, we start looking for a new apartment. Here in Singapore, the primary source of rent flats – it's PropertyGuru.

Luckily for us, they are providing some insights about price history. But, just looking at a list of prices, it's quite hard to guess what is an average price. For better perception, I've decided to visualize this table. Box plot would ideal for this purpose.

Example from Wikipedia

It's showing the distribution of your numerical data.

So. How it works.

I'm sending a link on any apartment
https://www.propertyguru.com.sg/listing/22013261/for-rent-the-seawind-telok-kurau
And in 10-20 seconds I get a box plot with a chart.

Super simple but very efficient. It's very easy to understand if the mean price is 2k and realtor is asking 3k, it's a sign that someone's trying to make it more expensive than it really is.

How I build Telegram Bot for budget and what lessons I have learned.

Dmytro Filipenko — Thu, 16 Jul 2020 03:51:30 +0000

Intro

For the past few weeks, I was working on a small side project – home budget in Google Spreadsheets, which I can easily add through Telegram Bot.

Filipenko

@dmfilipenko

Finally, I got some results. Still, a lot of stuff to improve. Allow creating a tables by your own, change records, but for my purpose, it's quite enough.

04:56 AM - 29 Jun 2020

1 6

Why did I decide to go with this stack?

Google Spreadsheets – compelling and extensible.
You can easily set up a massive variety of calculations and aggregations. It's an example of one of my formulae, which I'm using to calculate expenses per month per selected category.

SUM(
     IFERROR(
         FILTER(
            'Expenses'!D2:D,
            'Expenses'!C2:C =
            CELL(
                "contents",
                INDIRECT(
                     CONCATENATE(
                        "A",
                        ROW(

                        )
                    )
                )
            )
        ),
        0
    ),
    B1 = 'Expenses'!A2:A
)

So, before I started. I've created one document with three spreadsheets: expenses, category of expenses, and subscriptions. Then I've added all these aggregation formulas to calculate expenses by months automatically. And at the beginning, I've manually added all rows by opening google spreadsheets, usually from mobile, less often from the desktop.

But overall, this process took so much time, open google spreadsheet app, select expenses sheet, and start filling new row. It's working quite slowly from mobile.

So I've decided to automate this process somehow. First I tried to use Google Apps Script.

Google Apps Script allows you to get access to your spreadsheets, manipulate rows and columns. You could use your Javascript to write your Apps Script. It's a quite good solution, but I wanted to have more flexibility. I didn't want to operate in the context of Google App Script.

I went with a Telegram Bot API. To write it more easily Telegraf can help you to do this.

I first challenge was how to add Google Auth to a telegram bot. Google OAuth 2.0 documentaion provide you such images

Let me make it real simple.

I generate a google authorization link, with callback URL among query params, it's done by google SDK. Example: https://accounts.google.com/signin/oauth/oauthchooseaccount?access_type=offline&state=9999999&prompt=consent&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets&response_type=code&client_id=105404325697-1fs9rpeuhah4u4h8dfm4ma0p2ckpclb5.apps.googleusercontent.com&redirect_uri=https%3A%2F%2Fyour.url%2Foauth2callback&o2v=2&as=asda123casa&flowName=GeneralOAuthFlow
Then user follow this link, grant permission or deny
Google redirects me back to redirect_uri which was part of generated URL
On a server, I handle authorization token or error status and save it to DB.

Why it was most complicated part for me? I've spent a few days to realize how to connect Google Oauth + Telegram + NodeJS server. How to save tokens to DB, and exact tokens need to be saved.

Server

I'm using fastify as a web server, coz I need only API, and fastify doing a good job. For hosting, I've used Heroku. It allow me to reduce DevOps work. As far I'm using the free plan, they keep my server in a sleep mode if the server not doing any activities for 15 minutes. So, for my case, it's not a problem, but when I'm adding new expenses, I need to wait for 30-40 sec while Heroku woke up a server. For DB – mongo, I need to save user, all metadata for user, such as documentId, sheetName and some service information.
With Heroku is very easy to add monitoring, logging, error aggregation.

Telegram API

For working with TelegramBOT API I used Telegraf. It's quite powerful, but a lack of documentation potentially could be a problem.

Filipenko

@dmfilipenko

Before this, I tried it once-or-two, and I wasn't so amazed. But with the Expense task, I discover a few things.
1. First of all, NodeJS wrapper for a telegram API.
telegraf.js.org
The functionality of this library is quite powerful but lack of documentation.

02:54 AM - 22 Jun 2020

3 0

Filipenko

@dmfilipenko

Let me clarify. Documentation for a telegraf is quite good, in terms of what you can use. But, for me, it's more important not just what, but how you can use it. Examples, where each method could be used, it's also a significant part of proper documentation.

05:30 AM - 22 Jun 2020

0 0

For some questions, I used examples to understand how it's working. If you don't want to invent everything from scratch you can use Telegram BOT template.

What's next

I'm planning to add some text recognition. I want to take a photo of the bills and get a total number. I'm still trying to figure whats better to use for this task. Some OCR solution as Tesseract or EasyOCR. Also, one way to solve this to train some simple ML models on bills to find a boundary with the total price, and OCR tried to extract total amount.