How good is Excel?

Many advanced programmers despise VBA. Because programmers have contempt chain: Assembly >C >C++ >Python >Java and C# and PHP (these three tear each other) >VB >HTML. There is not even a place for VBA in this long chain of contempt.

But Excel+VBA is Turing-complete (thank you, @Octolet, for your excellent summary), so Excel+VBA does most of the things that sexy languages that programmers use to be cool do, often in a faster and more efficient way. Efficiency and elegance aside. And considering that most ordinary people do not have a programming environment (various rely on a variety of packages, a variety of OS compilation environment, and IDE), but using VBA, just need to open the installed Office, and then press Alt+F11 automatically enter the programming and execution environment; It’s even easier to record macros to solve the problem of writing programs, tinkering with simple code. Considering the perfect integration of VBA and Office software, VBA is incomparable in terms of convenience. Finally, Office+VBA is shareable and portable, and any program that passes the test can be easily executed on another machine. Other programs, even a simple “Hello World,” don’t necessarily.

So this article discusses all the cool things that Excel+VBA can do (and expand to Office+VBA as well) for Professional services and professionals from all walks of life who don’t write programs but rely heavily on Office.

A friend once said to me, “Excel doesn’t need programming at all. Excel geeks like me can solve all their problems with functions and custom functions.” In response to such comments, I recall an experience I had in elementary school. Unable to understand the value of imaginary number I (I ^2 = -1), I asked my father if I had eggs. My dad said, “When you get older and have more problems, you’ll know the value of I.”

1, automatic printing just entered the workplace of the new, as long as the father is not Li Gang, the basic have done the best actress (shadow = printing, all kinds of physical labor copy printing). In investment banking, in particular, it is not uncommon to spend hours or nights typing up documents before a big presentation or a big meeting with clients. And this happens over and over again with the same pile of documents that are constantly being revised.

The first project I joined BCG was to help a large enterprise design and implement KPI system from top to bottom. It involves dozens of departments from top to bottom, and there are about 100 KPI tables to be completed. These KPI tables are distributed in various Excel files. Our four consultants are tasked with:

• The basic format of KPI was set, and then each consultant was responsible for several departments, constantly modified the KPI table in Excel, and went to the parties and their leaders to discuss and modify it after printing it out
• Collect all the KPI tables in Excel files every week, print them out in order by department, and ask the HR chief in charge of the project to report the progress and situation

There was a laborious process of finding the target Worksheet in multiple Excel files every week, selecting the appropriate area as the output table, and printing more than 100 tables in a certain format and in a certain order. We used to collect all the latest Excel files by an Analyst every week with manpower, and then open the files one by one to select the appropriate Worksheet, select the region and set the format for printing. It takes almost an hour or two each time, and there is no guarantee of error.

So I wrote my first VBA program, and basically after the macro recording to change, no use of reference books and search engines, all rely on F1 and automatic prompts, so post a special memory. The realization of the function is to achieve all the functions of the human body automation. Press a girl and wait for the printer to come up with the results in sequence.



Later updates to the program included scheduling multiple printers to further improve efficiency and minimize the chance that printers could jam and cause queue errors.

2. Make charts and GIFs

Chart making is a necessary task for every Office family, making a good table is absolutely a sharp weapon for promotion and pay rise and lazy exercise. In response,How did braised chicken and rice become so popular? – Answer by Mingke Ho), using Excel+VBA to make a number of cool information maps, using VBA to color the graphics of each province.

(The coloring comes from an online program, and the giFs are made by the person who created them.)

At the same time, in order to further enhance the cool results, VBA is also used to make these continuous changes of the chart into GIF animation, but zhihu does not support GIF display.



3. Create complex analysis charts

The following figure is to study the conversion relationship between users of various models. The following complex formula is used to transform the one-dimensional conversion vector into a two-dimensional matrix.

=IF(ISERROR(OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0)/OFFSET($C$2,MATCH(CONCATENATE(RO W(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0)),"",OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",COLUMN(A4)),$D$3:$D$600,FALSE),0) /OFFSET($C$2,MATCH(CONCATENATE(ROW(A4),"-",ROW(A4)),$D$3:$D$600,FALSE),0))Copy the code

At the same time, VBA is used to color the matrix below in order to use the depth of color to represent the size relationship of conversion rate for easy comparison. One could certainly argue that conditional formatting is possible, but using VBA maintains maximum flexibility and efficiency.







4. Generate standard Word files according to formatting information


This is a project that I realized for my friends. Their laboratory is to study a certain kind of accident and identify the major accident, and finally release the official report in Word version. The previous work process was to complete the calculation and simulation in professional software, and finally fill in various key information according to the eight-part article of the official report, and finally write it into word file. The process of writing a report is tedious and untechnical, but repetitive.

Complete the main interactive interface and connect the computing software through Word+VBA as shown below. After the main information is obtained through simple interaction, the calculation is completed in the background and the main information is filled into word template of eight-part essay, and the report is finally completed. At the same time, the structured information is stored in Access database.



I hope to have a chance to@Raymond Wang 和 @ gold yuanWe will fully automate the writing of Termsheet and the writing of legal documents such as Termsheet to SPA and M&A.

5. Manage the distributed task flow through Excel, and output Excel forms to Powerpoint. This is to assist a large international automobile manufacturer to complete the launch of a new brand and its new model. However, launching a new model involves a myriad of branches: manufacturing, product, marketing, channels, marketing, public relations, finance and so on, as well as coordinating two European headquarters and two divisions in China.

The core task of this consultation was project management. I controlled the progress of the whole major project, reported the progress to the CEO in China every week, and found out the key nodes prone to problems to allocate resources. Four of us were assigned to take charge of several departments or project branches, and worked with the team to plan the process, draw the Gantt chart, identify milestones and timelines, assign leaders, and so on. When we came back to the office every day and summarized the progress together, we found challenges and difficulties. Each task line did not develop independently, but intertwined and influenced each other.

• Certain core people appear in multiple quest lines. For example: responsible for the budget of financial personnel, almost appear in each line in charge of the examination and approval of the relevant budget
• Milestones of some task-lines are related to each other as necessary conditions for milestones of others. For example, the off-line time of a new car will affect the time of the press conference, and the passing of relevant regulations and tests will affect the off-line time of the vehicle and so on

As the number of task-lines increases and the number of crossovers between them increases, the number of aggregated tasks increases exponentially, which is the problem we encountered during the project. So I used Excel+VBA to complete the automation of this work. Main functions:

• Automatically aggregates the scattered Excel files in the hands of the four consultants into one large master table, as shown below
• The tables in the hands of each consultant are divided according to department dimensions. After summarizing, different types of tables need to be output according to different dimensions, such as: output table by task line, output table by responsible person, tables of all delayed tasks, and tables requiring resources to focus on tasks, etc

On this basis, all the tables mentioned above in various dimensions (about more than 200) should be pasted into PPT according to the required format and submitted to the headquarters in China for weekly report and evaluation. The dense table is shown below. So I wrote a program to export the tables in Excel to Powerpoint, which simplified the work that a secretary needed hours to complete into one-click publishing, and I could complete the update of PPT in Excel.






The program quantity of this Project is not small, which is similar to writing a mini version of Microsoft Project for Project management.






Finally, the densely packed PPT in the figure below needs to be updated once a week, with a workload of nearly 100 pieces each time. However, the updates are basically completed automatically by Excel. Because the PPT template does not change much each time, I record these templates and only need to change the data in PPT according to the latest data in Excel when updating every week.

General Financial models calculate the final results based on numerous assumptions. While working for a top mobile brand, we had the reverse embarrassment. Originally is based on the ground sales staff of a certain level of service, calculation of the number of sales personnel needed; As a result, in the course of the project, the Head office had determined the Head Count of the number of sales and instead asked us to determine the service level according to HC. However, service level is not a single variable, but is determined by multiple factors such as the coverage of retail stores, the frequency of sales visits, and the depth of service during visits, and can also vary from one tier to the wireless city.

So we had to offer Excel+VBA magic weapon again. Firstly, the Financial Model is established according to the conventional ideas and the preliminary results of HC are obtained. Then write VBA program, according to different scenarios, different priorities and different weights to adjust the coverage of retail stores, sales visit frequency, visit service depth and other factors, at the same time set the acceptable range of these major factors, gradually approaching the preset value of HC.



If there is no program, the previous basic is to rely on manual adjustment to gather results, and because of the different scenarios, but also need to adjust for many times. And through the program, basically automatic completion, but also intelligent to set the priority and weight, without manual participation.

7. Download Bloomberg data and analyze it

Download tick data and order books of hundreds of target stocks through Bloomberg’S VBA API.



According to the mathematical model constructed by the realization, the calculation is completed in the background, and the above real-time data is converted into the real-time trading cost of each stock, which is displayed in the Excel interface most commonly used by traders, so as to facilitate traders to evaluate the current trading cost and optimize trading strategies.



8, conclusion

According to my calculation, I have been a consulting consultant in BCG for three years, and I have written tens of thousands of lines of VBA programs (all input by myself manually, without copying and automatic generation by the system), ranging from one thousand to several thousand lines of programs for each project. Finally, Excel is used as the central control interface, similar to EMACS. In Excel, you can control the printer, Word, Powerpoint and so on of the whole company at will, and automatically complete various tasks as well as data update and capture. Because Excel’s data is more structured, it has an advantage over Word and Powerpoint as a central control platform.

Some tips for learning VBA in this answer: How to learn Excel VBA quickly? – Answer by Mingke Ho.

Finally, some common modules in these programs were packaged to BCG Global IT, and BCG gave me a Sponsorship to study at Stanford GSB. In the comments line of the program are the names of several of my close friends, relatives and mentors. I wish them a safe life.

The last egg, you can also use VBA to paint oil paintings, zero basis to become with art works to flirt with younger sister han art youth (Excel has what strange skill and crafty, let you meet? – Answer by Mingke Ho).

_________________________________

For more exciting articles, see: Data iceberg – Zhihu column