A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA

Tuesday, September 5, 2023

A Predictive Model to Discover the Relationship among Stocks, Forex, and Commodities: Application of DEMATEL Method

 

“Search, follow, and focus everywhere that there is very high daily frequency, high Beta and high market cap then take your analysis to organize your own portfolio”


ABSTRACT

 To determine the relationship between stocks, forex, and commodities, a prediction model utilizing Microsoft Excel has been created. Three variables are measured to determine the relationship: the distance between points, the growth curve, and the direction of the motion. A portfolio of 20 assets has been chosen, comprising stocks, forex, and commodities. The daily return rate (change %) calculation is the only criterion to find the association. The DEMATEL method of decision-making has been used to determine the importance of the influence assets collectively as well as the high and low influencers, where the criterion is the direction of the motion and the possibilities are 20 assets because the direction of the motion is the most crucial characteristic to quantify the relationship. The priority of the influence assets as a whole as well as the influential relationships has been established for future study using the DEMATEL technique.

Introduction

 One of the simplest statistical calculations you can carry out in Excel is correlation. Even though it is simple, it is a huge aid in understanding the relationships between two or more variables. It's crucial to understand that correlation simply reveals how closely two variables are related to one another. However, a correlation does not always predicate causation. It implies that the statement that altering one variable will alter another is not always valid. Therefore, we have to use different techniques to detect a causal relationship such as the nonlinear regression, Structural Equation Modeling (SEM), factor analysis, multiple regression analysis, path analysis, and so on. Python, R, or SCALA are the most common programming languages to learn for predictive modeling that is based on machine learning.

In this project, a predictive model using Microsoft Excel has been designed to extract the influential relationships among the assets.

 Predictive Model

A predictive model is a type of data analysis that projects activity, behavior, and trends using both recent and historical data.  Of course, when it comes to the fundamentals of prediction, we resort to traditional and fuzzy logic and state, "If p, then q." Everything begins with the relationship between these two hypotheses, "p" and "q," and is then continued and evolved by the world's cause-and-effect chain. Since there is no such thing as absolute zero in the cosmos, this network of networks will never come to an end. When I first heard about classical logic in high school, I remember wondering why the English letters "p" and "q" were used. A few years later, after learning about fuzzy logic, I speculated that these two letters might act as an "eyeglass" to help us see and understand our surroundings more clearly.


In this project, a predictive model has been designed using Microsoft Excel.

How is the data processed by this model?

There are four fields for data entry in Step 1: "Date" and "Price" for each asset. Excel requires four columns since we wish to compare two assets.

Step 2: Because there are typically certain gaps between the dates of two assets, the system automatically matches the dates of both assets into one column when we enter historical data for any asset (date and price). This acts as a kind of timing index.

Step 3: In front of the index date column, the price of each asset will be split into one column.

Step 4: The rate of return will be calculated for each asset’s price

Step 5: According to the return rate, the direction of motion for both assets and two consecutive days is evaluated; if the assets are moving in the same way, the result will be "1," otherwise "0." The total rows of return rate will then be divided by the total number of "1"s. The outcome will be regarded as the direction's likelihood.

Step 6: When calculating the return rate for both assets in a single day, the distance between the points will be taken into account. If the difference between the two points is less than the distance change, the result will be "1," otherwise "0." The total rows of return rate will then be divided by the total number of "1"s. The outcome will be regarded as the distance's probability.

Step 7: The growth curve between two assets will be measured, where if the difference return rate between two assets in one day and the difference return rate between two assets in the next day is less than the growth rate, it will be "1" otherwise "0". Then total numbers of ‘1" will be divided by total rows of return rate. The result will be considered as the probability for the growth curve.

Remark: It appears that steps 6 and 7 are equivalent. Even though there is a significant difference in the return rates of two assets, it is still feasible that their growth curves are similar. As a result, I must separate the growth curve and the distance appropriately. (Please view the video below.)





Step 8: The measurement of the correlation using Microsoft Excel for the time series of return rates for both assets

Step 9: Using the sensitivity analysis in Microsoft Excel to reach the distance and growth rate with more than 95% probability

Step 10: The aforementioned processes (5), (6), (7), and (8) are once more computed to forecast a one-day delay between two assets, where we say: "If asset X is high, then asset Y will be high in the next day" and vice versa. Each of the three variables' probabilities will be taken into account.

The figure below illustrates each of the aforementioned steps:


Finding

Comparing the currencies "EUR/HKD" and "EUR/AED" is one of the better examples that have been discovered.

We can observe that this model and the prior 10-year historical data have a strong relationship; therefore, we may use them as the index for future investigations.

Please see below video:



Let me verify the information above using the following historical diagram that I have taken from the Yahoo Finance website:



Historical data of EUR/HKD (more than 10 years) 

Historical data of EUR/AED (more than 10 years) 


As you can see, all three variables have a good relationship with each other, so we can say that for a relationship rate greater than 80%, there are the following connections:

If "EUR/HKD" is high or low, then "EUR/AED" is high or low.

And

If "EUR/AED" is high or low, "EUR/HKD" is high or low.

 Or:


Of course, if a one-day delay is predicted, the proportion will drop significantly.


 DEMATEL Method

This project's goal is to use the DEMATEL technique, where a portfolio of assets has only been evaluated using one criterion, the return rate. This method aids in depicting the ad hoc relationship between the alternative assets as well as prioritizing them.

The Battelle Memorial Institute's Geneva Research Center launched the Decision Making Trial and Evaluation Laboratory (DEMATEL). DEMATEL plots a causal link map and offers visual answers to issues, making it possible to separate many criteria into a cause-and-effect diagram.

The procedure of the DEMATEL technique is composed of five steps, as follows:

Step 1: Obtain the pair-wise comparison matrix and establish a measurement scale, such as a Likert scale.

Step 2: Extract the direct relationship matrix of influential factors.

Step 3: Calculate the normalized direction-relation matrix.


Where:

N = Normalized Direction-Relation Matrix

D = Direct Relation Matrix

dij = a member of Direct Relation Matrix


Step 4: Compute the Total Relation Matrix from below function:


Where:

 I = the identity matrix

N = Normalized Direction-Relation Matrix

T = Total Relation Matrix

 

Step 5: Obtain the causal relationship.

The ith row and jth column of the complete relationship matrix T should be designated as Ri and Cj. The sum (Ri + Cj) indicates the degree of significance that element i (i = j) has within the entire system. But (Ri - Cj) shows the net impact of element i with (i = j). The net cause is revealed when the difference is positive, and the net effect is shown when the difference is negative. The values of (Ri + Cj) are plotted along the x axis, while (Ri - Cj) are plotted along the y axis.

Application of DEMATEL to evaluate the relationships among the assets

The DEMATEL method's general steps have been used as follows:

Prior to beginning step 1, we must choose our portfolio of assets, which should include the company's stocks, currency pairings, cryptocurrencies, and commodities. Since there are only a few widely used currencies and commodities, picking them up doesn't need to take too much time, but choosing stocks does require a plan for research and analysis.

Typically, we order the stocks based on the six factors listed below:

  1. High market capitalization (high market cap)
  2. High beta (beta > 1.1)
  3. Very high daily frequency on the daily prices
  4. Very large range of changes (return rate) per day during a period of historical data
  5. To track the change in volume per day during a period of historical data
  6. Price-to-Earnings Ratio (PE ratio)

The top four factors are more crucial than the rest.

I have chosen 20 assets for this project to create an initial matrix, and they are as follows:

A ={X1, X2, X3 …X20}

The following are two different initial matrix types that have been created:

- DEMATEL approach on asset return rates with no delay

- One-day delay in the return rates of two assets using the DEMATEL approach

Both types of assets are examined for their direction of motion during two successive days, and the likelihood that a relationship will exist between the two is computed in order to create the initial matrix. It is obvious that future studies will delve into detail and depth using the variables of distance between the points and growth curve.

For both categories, a Likert scale has been taken into consideration as follows:


DEMATEL approach on asset return rates with no delay

Because the probability results represent a biconditional logical connective among the asset return rates in this procedure, the pair-wise comparison matrix is symmetric. As a result, step (1) is as follows:


Step 2: By establishing the measurement scale, we will have the below Direct Relation Matrix:


Step 3: Normalized Direction-Relation Matrix


Step 4: Total Relation Matrix


Step 5: Obtain the causal relation


As you can see, the most important asset in this portfolio has been denoted by “X5.

One-day delay in the return rates of two assets using the DEMATEL approach

In this instance, the return rate between two assets with a one-day delay is analyzed in both directions. As a result, we can predict the return rate of an item by a probability percentage using the return rate of another asset recorded the day prior.

Step 1: To develop the initial matrix


For example, we can say that if the return rate of X1 is low or high, then the return rate of X13 will be low or high in the next day by a likelihood of 63.2%, and if the return rate of X13 is low or high, then the return rate of X1 will be low or high in the next day by a likelihood of 45.7%".

Step 2: Obtain Direct Relation Matrix


Step 3: Normalized Direction-Relation Matrix

Step 4: Total Relation Matrix


Step 5: Obtain the causal relation


Finally, the causal relationship can be plotted as follows:


Conclusion

What, in fact, is X12 or X7? Undoubtedly, it relies on the way I select my portfolio. In order to arrive at a logical conclusion, we actually need to further investigate, analysis, and compare a large number of different portfolios.

 The strategy for future research can be summed up as follows:

- Going to details by defining a threshold limited to select a new portfolio and examine the distance point to point and growth curve

- Select numerous additional portfolios and contrast them to verify the earlier findings.

- Pick the time period for the historical data. To determine whether or not the outcomes are the same, we must choose different time windows (what are the start time and finish time?).

- To forecast a delay of two days, one week, or even one month as opposed to one.

Finally, a plan for future study should be developed to evaluate particular cause-and-effect assets like X12 and X7. This plan should include reviewing annual reports, 10-K and 10-Q reports, income statements, balance sheets, and other documents as well as using tools such as DCF and MC methods for further analysis.


No comments:

Post a Comment