In this post I will briefly present an application which I created for a client at the beginning of 2015 using Excel VBA, advanced formulas and interactive charts.
What can it do ?
- The purpose of this application is to compare the companies from similar activity areas and create many charts based on the financial indicators of the selected company and it’s competitors.
How it works ?
- In the “Dashboard” the user can search the company based on the company ID (CUI) or based on the CAEN (activity area) number:
Search by CUI:
- The app searches the CUI in an external database (with data from the Ministry of Finance) and finds the searched company’s main competitors (max. 12) based on the turnover from the last year that we have in the database. If there are no matches in the 4 digits CAEN, then the user can decide to expand the search in the 3 digits CAEN or not:
- After presses “No”, the user can select to add to the analysis Dashboard maximum 8 competitors:
- After the user selects the companies and presses the “check button”, the app refreshes all the charts from “Dashboard” and the user can analyze the selected company’s financial indicators in comparison with the indicators of it’s direct competitors. Excepting the market shares (bottom right in the below image) and the comparison with the activity area averages (middle right), for every year there are 48 financial indicators grouped by categories (balance sheet, income statement, liquidity, solvency, performance, structure, productivity) and the application generates multiple charts for all of them based on the user’s selections. The user can also analyze the data at CAEN level. The items from the second combobox (indicators) and from the top right “charts” (performance of indicators based on activity area rankings) are dynamically refreshed when the user changes the first combobox (category of indicators):
- The CAEN chart’s source data is modified based on 2 groups of option buttons conditions (see code sample below to find out how it’s done 😉 ):
Search by CAEN number: the user inputs the CAEN number and then selects from that CAEN the companies to be analysed:
! This application has more other features (the colors of charts data are based on different criteria, export of data to other file which is imported in other application, if searched CUI does not exist in database the user is able to search one company which has the turnover the closest to the turnover input in an userform etc). It is used for advanced analysis of the financial evolution of companies ! I presented here just a few of the features only as a sample project !
Some printscreens from the “backend”:
A) Ranking data calculation (for the charts from Dashboard) with advanced Excel formulas:
B) Market Share data calculation and formulas. The analyzed companies are dynamically imported and ordered (with VBA). “Others” represent other companies within the same activity area. They are also highlighted with grey in the market share charts:
C) Formula for “thermometer” custom “charts”:
D) CAENs data:
E) VBA code Sample1:
F) VBA code Sample 2: dynamically populate combobox 2 when combobox 1 is changed and then change also the “thermometer” charts:
G) VBA code Sample3: modify pivot charts when the user changes the selected indicator:
H) VBA code Sample4: custom colors of charts:
I) VBA code Sample5: charts axis manipulated with VBA:
J) VBA Code Sample6: change charts source data based on option button selection:
K) VBA Code Sample7: for some formulas:
L) VBA Code Sample8: Copy some filterd data with Specialcells for CAEN in CAEN charts:
M) VBA Code Sample9: CAEN chart source data modified based on 2 groups of option buttons conditions:
!!! Important Note: All Sample Projects posts are examples of some apps created by me from beginning to end.All customer private data are hidden, the aim being to present briefly the app’s features, ideas, methods used etc.
