The Challenges of Writing Business Apps/Tools

As mentioned in my previous posts on this blog, my developer journey began as an intersection between the corporate world (accounting) and programming. I used to support my own accounting processes and help others with various VBA/Excel, Access, SQL tools and that's how the journey began.

Outgrowing my role as a Management Accountant in this technological sense, in 2019 I began studying and writing full time additional applications for business/accounting problems.

The basic idea of the tools is automating Excel-based processes in a more coherent database-app approach. There are a lot of struggles in my journey, but and I have learnt a lot, especially .Net technologies and SQL, and in the meantime, the web-stack using JavaScript and lately ASP.NET Core/Razor pages.

Clearly, the biggest challenge in all these is the mapping of real world processes (based on Excel and manual work) to an application. What's needed for this to be successful? In no particular order, my conclusions are:
1. very good understanding of the processes. This takes time and lots of communication/questions.
2. Understanding inputs and outputs. Data structure, types, formats, steps, validation, internal checks of the applications, stages of completion, reports, application logic and flow all come from inputs and outputs.
3. Thinking about the entity/DB structure and program flow.
4. From the mental mapping above come the DB structure, and then flow. Usually the flow is the succession of operations: input data, review data, edit, delete, search, etc, add more data, filter, prepare intermediary reports and then further add data, filter, edit if needed and then generate final outcome (reports, files). This cycle above can have multiple stages and checks/reports built within.
5. Validate the proposed process with the customer.
4. Discussing old methods vs new optimized process: lessons learned and areas to improve on in the new system based on feedback.

My big projects in the last 2 years are:
1.  Manufacturing Reporting/Statistics tool in MS ACCESS, on key phases of the production line. (manufacturing shop floor).
2.  Internal recharge system of the company, for distribution of indirect/administrative costs. Technology: C#, .Net, WPF/MVVM and SQL Server
3. My current project is an invoicing software that collects and invoices costs by employees. Using same technology as above. + Asp.net Core Razor pages
4. Another project for 2020 will be a VAT reporting tool to automate an Excel-based approach inside the accounting department, consolidating, filtering, automating data collection from several internal business units (big departments), and preparing statutory reports for Value Added Tax.

Some clear lessons to change the Excel-Based approach into a more efficient application are:
1. Using one database instead of many files, on a central server/Azure, for each app.
2. Mapping the entities from the excel files => as database tables/ and POCO entities.
3. All the automation of the repetitive processes go to C# code = > helper, data manipulation functions, using LINQ with lists etc.
4. Report generation, using ReportViewer tool, or ClosedXML to export excel files upon request;
5. Code re-usability.

Some architectural conclusions of the journey are:
1. need to manage the users of the application
2. implementing a reporting system, with parametrised queries, using SQL/Dapper/Reportviewer and/or simple Excel reports
3. implementing a concept of "Period": the accounting month in which all transactions take place. Afterwards the period is closed, and next month is opened for another series of transactions.
4. Implementing different types of Tables / entities by functionality:

• tables with reduced number of updates such as business units, customers, users
• tables with some  more frequent updates (such as employees, price lists)
• transaction tables (allocation keys calculated, monthly cost information, monthly invoice details, etc)

5. For transaction tables, insert the user and transaction date/time 

6. In some special cases, implement a history table for some entities for which the change log is important  (such as employee change history).

What do you think, what are the main points of producing such business/accounting tools? Would you do something differently from an architecture standpoint?

Why Microsoft Excel is So Popular - My Opinion

MS Excel is the most popular member of the Office family. Nothing can rival its popularity, and still today has no contender in the application world. Google Sheets, Libre Office, Open Office, are just pale imitations of what it can do. We are in the age of database applications, Sharepoint, Mobile and Web apps, and still, MS Excel flourishes.

As a former Management accountant myself, I used MS Excel a lot. Even having many internal tools, applications and databases, Excel was the most widely used, just for everything.


Still, it has some disadvantages that makes an application more viable, and I'll explore that later.

So, why is Excel so popular:
- no training is needed, you just open a sheet and type in information, write easy formulas
- no additional applications need to be installed (frameworks, databases, run-times)
- the file format is universal. Linux and Mac OS, mobile phones, web apps all can read and write to Excel.
- all kinds of activities can be done in Excel, such as:

• store data. Its sheets are like database tables, you can save data in them. Thousands of rows, and Excel can manage them quickly.
• filter, find, summarize, report on data. Filters, pivots, charts are very easy mechanism to analyze and search for data. No other tool can rival the ease of doing this.
• Formulas - it lets you build scenarios, budgets, calculate, optimize and simulations. The flexibility is just unparalleled.
• VBA : you can automate your work with programming. Very easy, using Visual Basic language.
• Lots of other functionalities: statistical functions/packages, connections with databases, and the web, etc
• Extreme flexibility: add comments, edit fields, change formulas, with a click.

Still, Excel has some disadvantages, mostly coming from its advantages:

• cannot deal with big volumes of data. Starting with 10k rows in a sheet, things become slow and unpractical.
• extreme flexibility has a cost. Data can be changed, formulas erased, information lost... and sharing the data is not efficient, leading to duplication, misunderstanding. The risks of running lots of complex data in Excel are very high.
• Filtering and data search functions still have some limitations. SQL, in my opinion, is more powerful. (and harder to write, also)
• VBA, the programming language and system of macros behind the data sheets is not efficient and clean code, scalability is an issue. You can use for simple things,but when there are multiple users, lots of data, complex business logic, a modern language such as C# can deal with these in a better way.

 

Somebody said that the 70% of all business logic of companies is in Excel. Working 15 years in a corporate environment (4 big companies), I can confirm this. Excel is the first option, when it comes to data analysis, reporting etc.

Do other applications have any chance to develop and flourish? Can they help to automate things and make life easier in a company? Yes and no.

Let's have a look to the possibilities for substituting MS Excel with an application.

Pros:

- in the case where the rules are clear and established, and the volume of data is big, an application makes more sense. 

Cons:

- where the data is small, and/or the rules are changing and much flexibility is needed, Excel is still a better solution.

So what's the right approach to substitute the bad part of Excel? Probably understanding the constant, rule-based, or algorithmic part of business processes, and write applications for them, while still using Excel in parallel to solve smaller, flexible tasks.

What do you think? Would you write an application to make Excel based processes more efficient?

Business Logic of an Application - My Experience as Newbie Programmer

If you read my other blog posts, especially the first ones, I explain there that my background is 15 years of corporate controlling (management accounting). And 2019 was for me a transition year to - writing accounting/business software. I accumulated some SQL/C# skills in the past years, and using my business knowledge and logic, I try to build some real tools from my previous workplace's accounting department.

I can state at the beginning that having an accounting/controlling background is very useful to understand the logic of my apps. Their goal is to automatize the work of accountants, to reduce to minimum the need of using Excel files and many emails for data collection, approval etc. So, my business background proves to be helpful!

Afterwards, comes the design and programming part.


First I design the database, using an abstract model of the data that can be used in the app.

Understanding table structures and relationships in excel, and the workflow, I can build the SQL tables and relationships, keys,  indexes, queries and reports that can be integrated to the application. I do this without having a formal training on how to build business applications, just following simple logic, then some database theory, SQL knowledge, normalization, etc.

Then I design the code and start writing it.

The models of the app are representing these SQL tables and relationships, and are the foundation of the application. Example: Business Units, Employees, Expenses etc.

Then comes the application layer, with MVVM -  a viewmodel that takes care of data coming from and going to the database, manipulating user inputs, checking, validating, iterating, etc...

Then there is the View of the application - WPF - for Windows interface, which is quite simple, user screens with tabs, tables (gridview), inputs, and reports, exports, imports of data. I try to make the user interface simple and easy to navigate, with a minimal risk to do stupid things. The users are happy, because they can use a centralized database app, and thus we can eliminate a lots of emails and excel files, which are very prone to error.

As I progress with my application, I go back sometimes to redesign the database (add fields, indexes, tables), and also the code of MVVM above. This is an iterative process for me, like continuous improvement.

Last step is to populate with data, and check. This is the most time consuming part, and can feedback to  database design and coding.

What do you think? Would you do something differently? Please feel free to give me feedback on the above topics.