Stock Market Analysis with Power BI
A term used most frequently in the financial sector is “stock.” Understanding stocks is necessary to understand what stock market analysis involves. A financial instrument called a stock represents ownership of a certain percentage of a corporation.
By examining and evaluating historical and current data, stock market analysis is a technique used by investors and traders to decide when to purchase and sell. Before investing, it enables investors to comprehend the security that a stock may offer. Using stock market analysis, investors and traders can make buying and selling decisions faster.
In this article, I’ll analyse the stock market using Power BI and use data visualisation to demonstrate market trends. I used stock market data from 1986 until 2021 for three companies: Amazon, Microsoft, and Intel. I’ll walk you through the procedure step-by-step beginning with data preparation, processing, and visualisation.
Step 1: Data Preparation
After launching Power BI Desktop, we want to import data. Power BI makes it extremely easy to connect to a variety of data sources, but for this article, we’re going to focus on connecting to Excel sources.
Simply, we can add our excel file by clicking on the button excel workbook, and it will import your file. For importing multiple files, we have to repeat the same step, and it will import all your files.
Step 2: Data Cleaning Using Power Query
Now that we have the historical data for Microsoft, Amazon and Intel stocks, let’s clean it up using the Power Query Editor. Query Editor allows you to dig in and manipulate the data. Here you can transpose columns, promote the first row to headers, create new columns, change data types and filter data.
Change the column datatype in Power Query Editor
In Power Query Editor, you can change the column data type by selecting the column that has the issue, selecting the data type in the Transform tab, and selecting the correct data type from the list.
As we can see, the Volume is in text format, so we need to change it to a whole number.
Removing Null Values
We might have null values in our data, so removing those is essential. If the value stays null, the averages will not calculate correctly. We can remove them simply by clicking on replace values in the power query editor and then typing null in the value to find dialog box.
Removing duplicates
After removing null values, we might check for duplicates in our data. We can remove duplicates by using the Remove Duplicates feature in Power Query.
Now that we have cleaned our data, the next step is to start creating visuals for analysis.
Step 3: Data Visualization and Insights
Power BI allows us to build a wide range of graphics, including bar graphs, area charts, line graphs, pie charts, stacked column charts, funnel charts, and more. Choosing the appropriate visual for the relevant information is crucial since it will serve as the foundation for our analysis.
Graph 1: These area charts show the comparison of the closing stock value of all three companies from 1986 to 2021.
Insights: Talking about the closing value of Microsoft, it can be seen that it is constantly increasing after 2016. Microsoft’s SaaS services and products are entirely cloud-based, which is why Covid-19 had no impact on its stock values.
Now that we’re talking about Intel, we can see from the graph that it reached its maximum closing stock value around the year 2000 since, at that point, the company was at its pinnacle and everyone was excited about it. But after the 2000s, the closing stock value declined because of Intel’s history of making too many promises and failing to deliver on them, which caused many investors to give up and lower Intel’s valuation than the market as a whole.
Regarding Amazon, we can observe that its value climbed significantly after 2019, which indicates that several big investors began investing in amazon in significant amounts.
Graph 2: The trade volumes of the three companies are compared in these pie charts, which also display seasonal variations based on quarterly data.
Insights: Various seasonal patterns can be seen through these pie charts. High volume indicates more interest in the stock and the presence of buyers and sellers in that stock. In all three companies, the highest volume of the stock is in the first quarter which suggests that there are more buyers and sellers during April-May-June.
In the third quarter, it can be seen that the volume percentage is the lowest among all the quarters which indicates that the buyers' interest has reduced in the stock and the uptrend is going to reverse during Oct-Nov-Dec.
Graph 3: This bar graph shows the impact of Covid-19 on the High stock value of Amazon.
Insights: As we can depict from the graph, Covid-19 had an immense impact on the high stock value of Amazon. Amazon was one of the companies that had a chance of developing and performing better in the harsh economic conditions brought forth by the spread of COVID-19. The spread of COVID-19 positively affected Amazon. Its high stock value was raised to 0.85 million in 2021.
First and foremost, Amazon’s performance in the global market emphasizes the concept of online services. Online services require minimum interpersonal contact in general. This means that the operations of Amazon were not significantly affected as compared to other companies around the world. On the other hand, Amazon gained more customers as people mainly looked for places to get services from their homes and minimized interpersonal contacts. The company had the best services that could offer services limiting human contact. Due to the lockdowns and restricted movements, the company provided a solution to the current market issues, thus earning a larger market share.
Create a calculated column with values from related tables
For the following visual, we created a calculated column combining values from two columns.
When you select the New column, the Formula bar appears along the top of the Report canvas, ready for you to name your column and enter a DAX formula. DAX formulas can leverage the full power of the model you already have, including relationships between different tables that already exist.
By deducting Amazon’s closing stock value from its opening stock value, we have built a calculated column to track changes in stock.
Similarly, we have calculated a new column i.e change in stock for Intel and Microsoft.
Graph 4: These line charts show the comparison of the change in stock value of all three companies.
Insights: As we can see in Amazon’s graph, swing or positional traders who hold on to trades for several weeks or months earned higher returns during the last quarter.
We can see a consistent trend line in the graph of Intel that depicts a rising stock value and doesn’t fall to a decreasing one.
In the Microsoft graph, we can see that the trend is up-and-down and that the traders are unable to generate a consistent ROI.
DASHBOARD:-
This dashboard shows the cumulative visual of three graphs and could be used to compare the various stock values of the three companies.
In this article, we’ve only begun to scratch the surface of what Power BI can do. Power BI presents itself as a powerhouse of functionality for more advanced users. It also offers the ability to create custom themes to be used in reporting. Power BI meets the needs of any and every type of user, regardless of skill level or business need. Power BI meets the requirements for any and every kind of user, regardless of skill level or business need.
