How a college intern used VBA to supercharge Zimbabwe SEC’s analytics

A college student from Harare, Zimbabwe, has shown that you don’t need to be a Bill Gates, or establish a global tech empire, or even use the most cutting-edge devops tools, to dramatically drive business-process efficiencies in a particular industry.

In fact, 22-year-old Liberty Bote wasn’t even a full-time staff member of the Zimbabwe Securities Exchange Commission (SEC) when he tapped Visual Basic for Applications (VBA) to drastically cut the time it takes to analyze massive amounts of trading data and flag suspicious stock movements  for the securities and capital markets regulator.

Growing up in Harare’s high-density suburbs of Glenview and Dzivarasekwa, Bote completed high school at Christ Ministries High before enrolling in the Harare Institute of Technology (HIT) for a Financial Engineering degree. Established in 1988, HIT prides itself on evolving from a vocational training center to a technical college, and ultimately becoming a university.

It was HIT’s reputation that earned Bote an industrial-learning internship spot at the SEC, which is responsible for monitoring and regulating a number of entities including the Zimbabwe Stock Exchange (ZSE), limited liability company Chengetedzai Depository Company, and Financial Securities Exchange (Private) Limited.

SEC’s Excel-based analysis took hours

Before Bote used VBA to modify the SEC’s Excel-based reporting system, agency analysts would spend a good part of their days going through a tedious process of sifting through price sheets and trade reports of the various platforms to generate a daily report, weed out and analyse suspicious trades.

“Coming up with this information, would take the analyst an average of two hours each day and up to four hours. Therefore we automated the whole process and now the average time expected for the automated process is one minute,” Bote says.

The SEC’s role is to, among other things, regulate trading and dealing in securities, and register, supervise and regulate securities exchanges. It also licenses, supervises and regulates persons licensed to do business in capital markets; encourages the development of free, fair and orderly capital and securities markets in Zimbabwe; and advises the government of Zimbabwe on all matters relating to securities and capital markets.

The SEC has also become an example of a Zimbabwean institution, though not a private enterprise, adopting technology to improve the day-to-day management of business processes.

The SEC’s market-monitoring, according to Bote, includes collecting and formatting data daily from ZSE, FINSEC and the Chengetedzai. The scope of information is vast; there are millions of shares traded every day on the ZSE alone.

Scope of trade analysis is vast

The information that the SEC gathers is mainly a compilation of all the trades, be they buy or sell deals, that take place every day, showing that settlements are all in order. Typically, each buy deal should have a corresponding sell deal and each trade should show the buying and selling broker. Related data includes the volume, that is the number of shares traded, and their value. The information is reported to SEC departmental heads.

Taking a cue from Chengetedzai, which compiles data comprising all trades for the year in a single workbook, Noel Mahombera, Bote’s supervisor during his one-year industrial learning stint, challenged the student to create an analysis solution covering all the data types and reports necessary for the SEC to accomplish its monitoring and supervisory mission.

At the time, SEC did not have a single analysis tool that processed all data relevant to its mission.

SEC reporting focuses on activity summaries, including statistics such as market capitalization, indices, turnover, volume and number of trades. For example, an analysis of top-five movers and shakers, in terms of value and volume, details the names of buying and selling investors, the brokers responsible for the trades as well as the consignment of the trades.

SEC tracks suspicious trades

The SEC said when a transaction is flagged as suspicious the first course of action is to gather all the related data, such as information on all those who traded in the involved security or securities, from the smallest trade to the largest, including the firms that brokered the deal.

“This can be very tedious as it involves scouring through a worksheet usually 1,500 lines of data detailing all trades within the day,” the SEC said, in a statement it released on the new analysis system devised by Bote. The SEC added, “the student developed a system to automate this process and at the press of a button, all the trades pertaining to a counter [stock exchange] is put in the table.”

Bote devised a way to speed up the collection, formatting, and analysis of trade data using VBA, designed by Microsoft to automate functions, and make programs like Excel — which SEC staff use to create and read reports — much more powerful. With its drag-and-drop interface, VBA is designed to let users rapidly automate processes by creating user defined functions, accessing third-party program functionality and Windows APIs through dynamic link libraries.

Liberty Bote

The SEC’s Excel-based reporting system, among other things, is supposed to highlight and examine suspicious trades – trades where certain aspects of the transaction appear not to follow SEC guidelines or legal provisions. Using selected VBA algorithms, Bote modified the Excel reporting system so that it could detect any  trades that have values falling outside the guideline parameters, and flag them as suspicious within seconds of the entire daily trading report being compiled.

In addition, before Bote’s modifications, the SEC tracking system normally would only make staff analysts and supervisors aware of trades that were very clearly outside the bounds of guidelines, or were flagged by complaints among actual market participants. Followup analysis, as the SEC stated, was time-consuming.

The modified system, though, can be used for more rapid fine-tuned investigatory analysis, for example, allowing SEC staff to quickly highlight all trades a specified investor, who has been involved in a suspicious trade, has dealt in.

“Due to the quantity of data, the tool I developed takes a few minutes to assemble the data into the desired form and prepare the analysis interface. This is on top of giving what I am calling the executive summary to the data,” Bote saysd. “At the pressing of buttons, graphs, pie charts tables, etc. pop up with analytical information.”

SEC CEO Tafadzwa Chinamo said he gave Bote an award for being the top student on the industrial-learning program at the agency. He said the system has been operational for six months now and managed to improve efficiencies.

“We could go through hundreds of pages manually because the information was available on spreadsheets” Chinamo said, adding “What would take three hours a day to produce now only takes two minutes.”

Now, having finished his industrial-learning stint to go for his final year at college, Bote promised to create more such solutions wherever he goes — with one already on his mind.

“If I had more time at the SEC, I would automate the process of returns-analysis and [trade-] board reporting,” Bote says.

Beaton Nyamapanda, a long-time Zimbabwean tech industry insider, described Bote’s solution as a a good example of a customization of basic enterprise technology that can end up being a game changer for the organization involved.

“Any automated system that can bring down a process that took 3 hours to perform to under two minutes is a good system,” says Namapanda, an information system specialist currently a software developer with a local firm. “These guys using this prepared information can then disseminate it easily to their other stakeholders in a timely manner.”