Implemented financial report for 2018 year.
There were requirements to investigate in complicated company database structure. Then create complicated SQL query for extracting sales data for all year. There was also task to do additional data processing, that was problematic to do on SQL query level. Final report should be provided for accounting department in xlsx format for future processing in Excel.
While I was working on that report, I have to solve additional problems:
As a result, I implemented complicated SQL query, divided to logical blocks with variables and WITH operator. I had to use WHEN/ELSE for handling different database structure. In some cases I hand to use pivot and crosstab for data processing. Some extra data was added manually using temporary table.
Final SQL report was quite complicated and heavy. It took about 10 seconds for 1 month interval. And it was impossible to run query for all year. There also were requirements for future data processing. Then I decided to export data by months into csv files, process them and build general report with Python script. I also used that python script for processing data, that was missing in main database. For example, some data was stored in excel files and google docs.
Another requirement was to convert payments from different currencies to one specific currency. Conversion should be done useng currency rate for specific date. Then I implemented parser for receiving currency rates from official sources(EUR, RUB, GBP, CAD, MXN).
When I generated final yearly report, it was used for passing audit check. Then it was used for new sales data analyse.
Besides that, I created other analytics reports, that were not so complicated. I used Metabase environment for data visualisation and user interface.
Technologies: Python, Excel, PostgreSQL, Metabase