In case you have missed the first video regarding Stock Trading, here it is. I list this here because it prefaces the following requirement for assisting with stock trading. This worksheet change event is very useful for triggering macros where a button is not feasible.

Stock Market Analysis with Python Scraping and Excel VBA Data Crunching – Demo

Using a combination of Excel VBA and Python coding, I have created a way to pull data based upon the current stock market by ticker.

Using VBA, I make the necessary calculations that support my objectives.

The whole idea behind this project is to make sense of the stock market and create something that would help me with my Robinhood or any other brokerage firm.

The goal is to take the emotion, fear, and greed out of the stock trade process and keep it data based, factual using the scientific method.

This lets me know based upon my decision when I enter the trade, the points at which I exit that trade. It really comes down to risk management.

I am not an expert at trading stocks.

This content is for informational, educational, or entertainment purposes only, not personalized professional advice.

This is a hobby and scientific experiment for me that I decided to feature on the channel.

Investments do involve risk, including the loss of principal, and past performance does not guarantee future results.

I choose to develop these tools for survival and personal growth.

The Basics of Stock Trading

Stocks move up, and move down, if not then a “sideways trend” is evident.

My goal is to benefit from multiple stocks that trend upward (bullish),over a specific period.

Stock market trend: the overall, prevailing direction of price movements—upward (bullish), downward (bearish), or sideways—over a specific period.

Traders and investors identify these trends using tools like trendlines and moving averages to align their investments with market momentum, seeking to buy during uptrends and sell or short during downtrends.

Technical Aspects of This Process

The Worksheet_Change event in Excel VBA will trigger automatically when the value of a cell or cell within a range of cells on a specific worksheet is modified by a user or external link.

The code must be located within the specific sheet Module in the Microsoft Excel Visual Basic Editor (VBE).

We currently use Microsoft 365.

How Worksheet_Change Events Work

The action occurs when a cell is edited or changed manually or via a data validation dropdown. It does not trigger on recalculations (use Worksheet_Calculate for that) or when cells change due to formatting.

I personally like the worksheet change event feature because there are some situations where a button isn’t the best or optimal trigger for an event.

Practical Real World Application

I have a spreadsheet that keeps track of active trades on the stock market.

The Requirement:

If I close a position by exiting the trade, the exit price, date of position closure gets noted and the line gets moved to the archive section. Win, loose or draw, I want a record of this information for future analysis.

My Options:

A.) I could manually copy that row to the next sheet

B.) I could use a macro to do it for me while maintaining the structural integrity of the spreadsheet, and the accuracy of the data transfered.

C.) I could do the same as item B, but instead of using another sheet, move it to a database instead.

Practical Real World Application

My Best Choice of the Available Options:

C.) I could do the same as item B, but instead of using another sheet, move it to a database instead.

When I look at a complex situation, I ask myself the question,” Is this something that can be worked out on Notepad? Would an Microsoft Excel or Google Sheets spreadsheet be necessary in this case? Where a spreadsheet falls short, would a MS Access database be necessary for this use case?

MS Excel vs MS Access

An Excel spreadsheet limits us to 1,048,576 rows by 16,384 columns (XFD) per worksheet for .xlsx files

It has been my experience that Excel spreadsheets with a significant amounts of data, load and run slow, crash, and often results in lost or corrupted data. Corrupted data isn’t all that easy to recover.

Best practice dictates:

Use Excel for quick analysis, flexible calculations, pivot tables, and small, flat datasets.

Use Access (a relational database) for large volumes of data (thousands of rows), complex relationships between tables, multi-user collaboration, and strict data validation to ensure integrity.

As with Microsoft Excel, I have the option to create an interactive dashboard to analyze historical trade data, have a complete record of my trades, and be able to make better decisions based upon real world data.

I have never created an interactive dash board, however, it is something I will learn about and create myself on this topic further down the line.

Key Resources:

Yahoo Finance

https://finance.yahoo.com