Analyse monthly figures with Excel VBA
I work with Excel in my day job and am always on the lookout for ways that I can automate the work I do. This need is especially apparent because I have monthly reports that I must prepare and it is a huge time saver if I can automate the work that goes intro preparing the reports.
Last week I was given the task of analysing figures in a large spreadsheet that I receive from a third party every month. I prepared the analysis, as was requested of me, and was then told that I will be required to d o this every month.
Because I want to save time, I looked at ways to simplify the task, so I decided to look for a way that I could use Excel VBA to perform an analysis with the touch of a key.
The screenshot below is an anonymised version of one of the many reports that I prepare and put on a dashboard:-
I thought about it, and wrote the program below:-
- The program retrieves the data for the current month, last month, and last year.
- It then subtracts the last month and last year values from the current month and determines if there is an increase or a decrease.
- It prints out the values on a blank cell in the spreadsheet and advises if the values have increased or decreased from the previous month and year.
After the code has been saved and the program is run, the analyst will be prompted to enter the row number he would like to begin the analysis with, and this row number should be the current month:-
Once the row number has been entered into the popup box, the program will execute and print out the analysts from cells L1 to O7:-
The spreadsheet above is one of several that I have to produce every month, but I intend to modify the code for the rest of the spreadsheets that I have to prepare in an attempt to expedite the process and free my time up for other areas.
Have created a video to accompany this blog post, and it can be viewed here:- https://youtu.be/70G42bJkmKk
