I must admit, the first time our client explained what they were trying to accomplish, I was a bit overwhelmed. There were hundreds of Excel workbooks. Each workbook contained stock price data for 1,800 stock symbols. Each week, our client was attempting to calculate 19 different price movement indicators for each stock. The goal was to find the best buying opportunities in this mass of data.
The task at hand was to first organize the data and then to for the complex price movement indicators. Because this project involved so many financial calculations, we decided to keep the logic side of things in Excel. Because of the vast amounts of data, we also decided to utilize a database instead of trying to maintain all this data in an Excel workbook.
Here is how we created a tool that would surface key stock market opportunities for our client:
The Database
As we do with many projects, we chose to use a SQL Server database hosted on Microsoft Azure Cloud. SQL Azure is a very affordable way to provision a high-end database with excellent security and very scalable performance. After reviewing the data being captured in the Excel spreadsheets, we designed several database tables to manage the data efficiently.
The Excel Application
The Excel workbook we developed for this project has two primary purposes. The first is to serve as the interface between the web-based stock market prices and the database. Through API and SQL statements written in Excel VBA, we retrieve the desired stock prices from the web and pushed them into the database. The second purpose of this workbook is to perform the financial calculations on each of the stocks. The stock price data is retrieved for a set period and is then put through our calculation logic to produce the proprietary scoring results for each stock based on 19 price movement indicators. The results of these calculations are then stored in the database for easy reference and reporting. The workbook procedures are controlled by a set of menu buttons.
Stock Price Automation
If you have ever hired a VBA programmer to retrieve stock prices from the internet, you probably know that the go-to source for this data was Yahoo Finance for many years. You probably also know that this free service ended shortly after Verizon purchased Yahoo. As luck would have it, the Yahoo Price data became unavailable during the development stage of this project. We had to pivot our approach to another online provider of price data which required more complex API calls. Thanks to the versatility of Excel VBA and our team at ThesisTech, we were able to which could utilize this new web service and provide the necessary stock market data.
The Financial Calculations
The financial calculations required to create the desired outputs of this process were very complex. Each financial calculation had its own complexity and challenges which had to be worked through by our team. The complexity grew even greater as these 19 financial metrics were then combined into a single scoring metric for each of the 1,800 stock symbols. Excel VBA processes these calculations quickly and provides the horsepower needed to complete the necessary calculations.
Summary
The stock market produces huge amounts of data every day. Analyzing this data often requires an automated approach. We helped our client build a tool to organize a large dataset and surface the key market opportunities found in the data.
About the author: Brian Busscher is the Founder and CEO of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about how the right can help drive your business forward, please contact the Thesis Team technology consultants at (888) 705-7253.
Find other posts here.