Designing an Expert System

CSIS-114: HW 6

Designing an Expert System

Using past performance as a model, it is possible to create a system that can automatically make good decisions. Consider the simple case of an investment fund that fluctuates with some pattern or consistency. A simple system could be designed that recognizes when the fund is abnormally low and abnormally high. Thus, the system could automatically buy the fund when it is low and sell it when it is high. The system could do this on a per-minute basis, where it might be impossible for a human investor to make such rapid a decision.

However, in order to make the Expert System work, you must carefully consider patterns or trends, and design a system that accounts for these patterns and trends. While an Expert System may work well for some funds, it may fail for others. In this assignment, you will modify an expert system so that it can handle more than one type of pattern.

A Simple Expert System

In the file hw6.xls, you will find a spreadsheet with two tables and two charts:

The Data Table contains the price of 4 different funds over time. The price is recorded about every 15 minutes. The Data Chart shows the funds graphically.

The Expert System Table contains information about the rolling average from the last 10 price changes. It also includes a hi and low margin. These margins can be adjusted by changing the margin factor in the top right-hand corner of the table. The Expert System Chart shows the actual fund price compared to the rolling average and the hi and low margins.

Here is how the expert system works:

• The expert system will recommend that you buy the fund when the actual price goes below the low margin. Here is the general excel formula:
  =IF (PRICE < LOW, “yes”, “no”)

• The expert system will recommend that you sell the fund when the actual prices goes above the high margin. Here is the general excel formula:
  =IF(PRICE > HI, “yes”, “no”)

• The expert system also keeps track of whether or not you own the fund at a particular moment in time. This is hard to calculate. You have to consider whether you purchased the fund in the past but did not sell it yet. If buy equals “yes” at that moment in time, they you own it. If sell equals “yes” at that moment in time then you don’t own it. If both buy and sell are equal to “no” then simply display the previous value of own, i.e., the value of own from the cell above. Here is the general excel formula:
  =IF(BUY="yes","yes",IF(SELL="yes","no",PREVIOUS_OWN))

Notice how a compound IF statement is used to test two different condition in one formula. You will have to create a similar formula.

• The expert system also keeps track of the price at which you bought the fund and the percent increase if you were to sell the fund. These formulas are also tricky, but they are given to you.

• Conditional formatting is used to highlight “yes” and “no” for the buy, sell, and own columns and to highlight a positive or negative gain.

• At the top right of the spread sheet is the final value of your investment if you follow the advice of the expert system.

Task 1: Try the Expert System

Copy each of the 4 funds prices into the ACTUAL column. Then for each fund, try different margin factors to see if you can grow your investment. Look at the Expert System Chart so see what is really happening to the fund and the margins.

Questions:

1. Which funds were you able to grow using the expert system? For each fund that grows, what was the margin factor that produced the best growth? What does this tell you about the fund and how it fluctuates?
   

1. Which funds were you NOT able to grow using the expert sytem? Why is the expert system failing on these funds? Be specific.
   

Task 2: Improve the Expert System

Copy the Expert System Table to another tab. On the bottom tab, right-click and select Move or Copy… Check the copy box. Make three additional copies. Rename the tabs Modified 1, Modified 2, and Modified 3.

Using the copies, implement three different Expert Systems.

1. Modify the IF statements so that you only buy the fund if the price decreased from the previous price. Then, only sell the fund if it increased from the purchase price.
   Question 3: Does this improve the growth of the funds? For each fund, compare the growth of the simply expert system with the modified version you just created?
   
2. The best time to buy the fund is when it reaches what’s call a local minima. This is a valley where the price is abnormally low and likely to increase. The best time to sell the fund is when it reaches a local maxima, i.e., a hill where the price is abnormally high. The simple Expert System failed because it can only detect sharp hills and valleys, not rolling hills and valleys.
   How can you detect the top of hills and the bottom of valleys. A hill occurs when the rolling average has been increasing but then starts to decrease. One way to detect this is to consider different rolling averages: One for the last 5 prices, one for the last 10 prices, and one for the last 20 prices. If the 10-price rolling average is higher than both the 5-price and 20-price averages, then you might be at the end of a hill, i.e., time to sell. If the 10-price rolling average is lower than both the 5-price and 20 price, then you might be at the end of a valley, time to buy.
   This strategy can be easily implemented by computing the 5-price and 20-price rolling averages instead of the margins. Then, all you have to do is re-write the IF statement for buy and sell to detect the proper conditions.
   Question 4: Does this change improve the growth of the funds? For each fund, compare the growth of the simply expert system with the modified version you just created?
   
3. Perhaps its better to consider ranges other than 5, 10, and 20. Try another set of ranges. For example 3, 7, and 15.
   Question 5: For each fund, compare the growth of the simply expert system with the modified version you just created?
   

Deliverables

1. Answer question 1-5 in detail and submit a Word document via Blackboard. Questions 3, 4, and 5 can be answered with a simple table that compares the original expert system with the three different version that you implemented. For each fund, simply show the final investment amount.

2. Submit your Excel spreadsheet via Blackboard

Grading

There are no expectation on performance. If you answer the questions in detail and properly implement the three modifications, you will receive 100. Deductions will be made if there are errors in your formulas or if your answers are not detailed or incomplete.