Before we begin,
I'd like to preface this post by saying that there is no actual code or implementation details here. This is just to develop a high-level understanding of the project. Look for section 2 to start programming!
Intro:
Hello, and welcome back. In this new series, I hope you will join me on my process of building-out something non-trivial with VBA.
Before we begin, I would like to mention that software development is complex. So complex infact, that there is no realistic way for someone to hold all the design and implementation aspects in your head. It's more of an explorative/iterative process than a descriptive process. I will probably jump around from frontend to backend (more on that later in this post), design and implementation, SQL to VBA; but, I won't show it in the tutorials because it's easiest to present and understand the development in a compartmentalized fashion. Always remember, the process of software development is that of adaptation and problem-solving.
Let's get into the nitty-gritty of it.
Overview:
The highest-level abstraction (the hiding of all the details so that communication, understanding, and implementation of an idea is easier) of modern software architecture is that there are two components:
- The frontend (that which people see -- the "tip of the iceberg"),
- and the backend (that which people don't see -- usually 85-95% of the "do-stuff" code).
The frontend, which today is some sort of GUI ("gooey", Graphical User Interface), is where the user interacts with a program. Everything you see in your web browser is the frontend of my website. The language HTML (Hyper-Text Markup Language) is used to tell it what to display, and the language CSS (Cascading Style Sheets) is used to tell it how to display something. However, these languages are generally used for web-based applications.
The backend usually consists of a database to store information and the business logic in code format. This could be a MySQL database for information storage, and PHP for the programming language to perform the computations (the technologies used in WordPress).
There are a number of technologies to use for either frontend or backend development (some technologies are better at certain things than others), but we are going to use the Office suite.
Overview, More In-Depth:
The application we are going to build could be described as an ETL (Extract, Transform, Load) application, to borrow a term from data science. We're going to be pulling raw data (extract, or more commonly known as data-mining) from the internet, "cleaning" (transform) and storing it in an Access database, and then presenting (load) it to a user in Excel.
I know this is somewhat of a dead horse for web-scraping (data-mining HTML files via the internet) tutorials, but we're going to be using stock information because working with this type of data interests me.
I see the application having three major components:
- A VBA script to pull information from the internet,
- the Access database where the information is stored,
- and the Excel file where the information is presented to the user.
In the next section, we are going to be implementing the script to pull the stock data from the internet. I hope this explanation has given you a decent understanding of how these parts will work together to form the whole application.
Thank you for reading my post! 🙂