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

d_ij = 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 i^th row and j^th column of the complete relationship matrix T should be designated as R_i and C_j. The sum (R_i + C_j) indicates the degree of significance that element i (i = j) has within the entire system. But (R_i - C_j) 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 (R_i + C_j) are plotted along the x axis, while (R_i - C_j) 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 ={X_1, X_2, X_{3 …}X₂₀}

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 “X₅”.

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 X₁ is low or high, then the return rate of X₁₃ will be low or high in the next day by a likelihood of 63.2%, and if the return rate of X₁₃ is low or high, then the return rate of X₁ 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 X₁₂ or X₇? 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 X₁₂ and X₇. 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.

Accounting Software for Dealers, Traders, and Brokers

 

Introduction

 When we are speaking about accounting software, it is expected to manage, record, and report the day-to-day financial transactions of a firm. Of course, there are many types of accounting software for the specific needs of an industry or business. This accounting software can be used for multi-currency transaction recording.

The purpose of this project is to control and record daily financial transactions and business deals in multi-currency for reporting daily customer's account balances by using accounting software based on Microsoft Excel.

How does this software wotk?

The most powerful and distinguishing feature of this accounting software is its ability to manage and record business deals, and by recording the appropriate format of any deal, the system automatically calculates the complicated tasks.

Let me start with some examples:

Suppose you are managing a network of customers, deals, and financial transactions just like the one cited below:

 You agreed with your customer (X), who will transfer EUR 480,000 on behalf of you due to your Performa Invoice (Buying some industrial machinery), while you will pay him Dirham (AED). Your deal is as follows:

 Company "X" will charge you a fee of 0.4% for your EUR remittance (1- 0.004 = 0.996), and this company will also consider the ask-bid spread of 35 pips on the rate of EUR / AED = 4.0856.

 How can you calculate the cost of this business deal, which is your loss?

 Solution:

Let me record this deal in a cell of Excel as follows:

Purchase 480,000*1.004 EUR @ 4.0856+0.0035 AED

 Or

Purchase 480,000/0.996 EUR @ 4.0856+0.0035 AED

 Please see the below video:

As you can see, this software automatically calculates the amounts of sale, purchase, and Profit or Loss where we have:

Your purchase (Received) of EUR = EUR 481,920

 Your sale (Paid) of AED = AED 1,970,619

 Your loss = AED (9,531)

In the next business deal, you will sell this industrial machinery to company "Y" for 2% profit plus an ask-bid spread of 45 pips, and you will receive Russian rubles (RUB) from company "Y."

We can calculate your profit due to this deal as follows:

The deal will be:

 Sell 480,000*1.02 EUR @ 101.2297+0.0045 RUB

 See below video:

Then, Mr. "Z" will pay you CAD for all amounts of your RUB by 0.135% less than the rate of CAD/RUB = 68.6724.

Definitely, you will gain from this deal as follows:

This deal will be:

 Sell 49,564,264 RUB @ 68.6724/1.00135 CAD

 See below video:

Finally, the time is over, and you should pay your debt to company "X.". In this case, you have to sell to Mr. "W" the amounts of CAD to receive Dirham. He will charge you a remittance fee of 0.3% and an ask-bid spread of minus 30 pips on the rate of CAD/AAED = 2.7778 as follows:

We can record this deal just like the one cited below:

Purchase 1,970,619*1.003 AED @ 2.7778-0.003 CAD

 I will also record this business deal into accounting software, and then we can glance at the final account balance as follows:

Here is a screenshot from account balance:

According to the above balance account:

 You have a debt to company "X" equal to AED 1,970,619

When you receive the industrial machinery from company "X" and deliver it to company "Y", the amounts of EUR 481,920 and EUR 489,600 will automatically be cleared.

You should receive the amount of RUB 49,564,264 from company "Y" due to the delivery of the industrial machinery.

In exchange for receiving CAD 722,724, you are expected to pay the amount of RUB 49,564,264 to Mr. Z.

Your debt to Mr. W is equal to CAD 712,315 and your credit is equal to AED 1,976,531.

 You only need to record the deals in the mentioned format into a cell, and then you will automatically have all account balances on a sheet of Excel. I have named this model Artificial Intelligence by Microsoft Excel.

The next step is to keep track of the accounts that your customers have paid and received in order to update the balances.

For clearing accounts, Mr. "W" will transfer the amount of AED 1,970,619 on behalf of you to the banking account of company "X.".

When you deliver the machinery to company "Y", the amounts of EUR 481,920 and EUR 489,600 will be settled.

The company "y" will pay the amount of RUB 49,564,264 to Mr. Z," and he will pay the amount of CAD 712,315 to Mr. W on behalf of you.

The difference between the amounts of AED 1,970,619 and AED 1,976,531 will be equal to the fee charged for the business deal with Mr. W," which should be cleared.

Now, you will have the updated balances for all your customers.

As a result, your total profit will be equal to CAD 10,409.

Please see the below video:

If you have any questions, do not hesitate to send your request to my email:

soleimani_gh@hotmail.com

Discrepancy Analysis (2): A Model Made by Microsoft Excel

 

Sometimes finding the discrepancies between the accounts is easy, but that can be very complicated as a real case in which to extract the area of discrepancies would be impossible.

For instance, suppose your customer settles the sales invoice in partial payments during a period of time. On the other hand, it is possible; the partial payments made by your customer are not exactly equal to the total sum mentioned in the credit section of your bank account.

In fact, here we have two big problems with auditing the accounts:

 1.  In the credit section of a bank account, we have the total sum which has been divided into many partial payments on the debit section of a bank account.

 2. The above total sum is not exactly equal to the partial payments.

 

Following to the article of Discrepancy Analysis (1): A Model Made by Microsoft Excel , here is a new analytic model that it can help us to fix these two problems.

 

How does this model process the data?

 

The process of data will be accomplished into three phases:

 

1. To accumulate the data of Debit and Credit (Contraction)

 

2. To solve many equations simultaneously

 

3. To extend the data of Debit and Credit (Expansion)

 

Phase (1) when you enter data into the appropriate columns, the system automatically sums all amounts related to each day. Then, depending on your choice, this model accumulates the amounts the rows of debit and credit. For instance, suppose you have 400 rows of data for debit and 460 rows of data for credit. You are willing to get only 10 rows for debit and credit. When you enter your order of 10, the system automatically accumulates all 400 and 460 rows into 10 rows.

 

Phase (2) the data of phase (1) is transferred into two columns of “Debit” and “Credit” but maximum 10 rows. The chosen number of rows depends on the capacity of system (Type of processor, RAM and so on).

Here, we have 1024 equations that should be solved simultaneously.

Suppose there are below data on two columns:

The general format of equations can be as follows:

Phase (3) the result of phase (2) will give us some scenarios which are the solved equations but accumulated .Therefore, we will choose a scenario and transfer it into a model of phase (3) to extend the data (Expansion).

 By doing each three phases, we will finish a step. We have to repeat several steps to find out the date and the location where the discrepancies occurred.

 How does this model work?

 Firstly, we should enter the data of Debit and Credit into two columns. Secondly, we need to change two independent variables of “error” and “milestone” to achieve the best scenario.

 

What are the variables of “error” and “milestone”?

 “error” is following below formula:

By changing “milestone”, we are able to obtain the desired accumulated data in ideal row numbers. The final step is to copy and paste the results into the model of expansion and to repeat previous steps.

Let me take some examples:

 1. I will fill out the data in column Debit and also the same data in column Credit. Therefore, we do not have any discrepancy in the first step.

 2. Suppose the operator of accounting software took a mistake to record $ 10,000 instead of $ 1000 (Yellow highlighted). We have to change Milestone until we achieve a fixed date. Here is the location of the discrepancy.

 Please see the video below:

3. Now, I change $ 5000 to $50,000 in row 1540 and column Credit. By the same try, we will find the area of the discrepancy.

 

Please see the video below:

As you can see, we do not need to go to phase (3).

 4. In this example, suppose you have received a total amount for three invoices sent to the customer while there is the discrepancy between the total receiving and three invoices.

On column credit and row 1545, I add $2,500,000 but on column debit and rows

1545, 1546 and 1547, I add $ 1,500,000, $ 500,000 and $488,905. Here, when I change the error by more than $9000, I can access to a focus area, otherwise I receive three locations that show three areas of the discrepancies.

 Please see the video below:

 

NOTE:

Since this model is under test, interested people who have real

cases, do not hesitate to send their data to my email:

soleimani_gh@hotmail.com

Do not worry about the security of your data, because you can multiply or divide all the data of columns debit and credit by a positive real number such as 0.058, 3.7, 6, and so on, and then send it to me.

 

Discrepancy Analysis (1): A Model Made by Microsoft Excel

 

Suppose you are expected to control the balance of the accounts between the sales of the store network and the accounts receivable in the banking network as follows:

The above flowchart says to us, you should release the customer’s order to conform to the invoice when you receive the bank receipt sent by the customer. 

Nowadays, people are able to make fake receipts by using Artificial Intelligence tools. On the other hand, it is possible that a customer will send you repeated receipts. Thus, you need to take a rapid check of the invoices and all bank accounts before sending the goods to the customer.

This analytic model helps you to find out all discrepancies very soon. 

By using this model, you will take two types of the discrepancy analysis as follows:

1. When there are different amounts of data on the sales and accounts receivable in the banking network

2. When there are the same amounts of data on the sales and accounts receivable in the banking network but the number of repeated amounts is different between the data on the sales and accounts receivable in the banking network

Note: This model does not cover the below conditions:

- If your customer settles the sales invoice in partial payments during a period of time

- The partial payments made by your customer are not exactly equal to the total sum mentioned in the credit section of your bank account

These conditions will be covered by a new analytic model that I will post next time. (See Discrepancy Analysis (2): A Model Made by Microsoft Excel)

How does this model work?

You only need to collect the sales data from all stores and banking data from your entire bank network accompanied by the dates and copy them into appropriate columns of this model.

Note: There are varieties of applications for this analytic model, in which you can compare two columns of texts or two columns of dates. Therefore, before starting, you need to define four titles for columns as shown in the below figure:

Examples:

I copy and paste the data into 4 columns. As you can see, firstly there is not any discrepancy. But I manually changed some of the amounts in column sales and then changed some of the amounts in column account receivable. You can see the results in Discrepancy Analysis_2.

In the next step, I will repeat some of the numbers in both columns. You can find the results in Discrepancy analysis_ 1. For instance, when you see “3 or -3”, it means that there is this amount in both columns but in one column 3 times more than another column.  

Please see below video:

See this Video on youtube:

You can also utilize this model to find out the discrepancies among the texts.

Please see below video:

See this Video on youtube:

If you have any questions, please do not hesitate to send them to my email address: Soleimani_gh@hotmail.com

Solving COMPLEX CAPTCHAs by Using an Analytic Model Made by Microsoft Excel

 

Nowadays, there are three types of CAPTCHAs, including text-based, picture-based, and sound-based. One of the most complicated picture-based CAPTCHAs is to match a given number which is equal to the total sum of many other numbers into a picture.

Suppose CAPTCHA gives you a picture of twelve dices and asks you to match a given number such as 48 which is equal to the total sum of some dices in the picture.

It could be impossible because you have to solve 2^12 scenarios (4096 scenarios) in a very short time.

This analytic model can help us to find the answer.

As you can see, we enter the given number into cell “B2” then all numbers of the dices into cell “B3”. If the status is “OK”, this is the answer. Otherwise, the answer is negative or “REJECTED”.

Please see the video of this model:

First, I assume all 12 dices are equal to 6. Then I changed some numbers where the total sum would be equal to the given number.

When the status returns to “OK”, we can obtain one answer or more, which are the alternative answers. 

If you have any question, please do not hesitate to send it to my email:

Soleimani_gh@hotmail.com