Chapter 5. Formulas
Your First Quote Sheet Formula
Traders could use ErlangerQuote all day and never enter or use a single formula. Yet once you learn how to use formulas you won't understand how people can trade without them. You might be thinking that there is no way a formula could ever be very important to you. But let me give you a simple example that will make you a believer. Or at least get you motivated.
When Last Is Not Enough
How many times a day to you turn on CNBC and see the current value for the DOW, Nasdaq and SP500 in the little ticker at the corner of the screen, and think to yourself "just how high did those indexes get today?" Or wonder, "Are we off the lows, or off the highs?" Or "How far is this current price from the high of the day?"
Of course the guys at CNBC know you want to know that, so every 5 or 10 minutes they tell you with their great graphics and sound bytes, but not always. What if you just want to know when you look at your workspace? In ErlangerQuote this is an easy thing to do with a simple formula. First, we know that a quote sheet can show us the net gain or loss for the day by using the fields: Net and Net%. To add these columns just right click->Fields->Value->Net and repeat this a second time to add the Net%.
Using the formula language we can create columns that will show us other information including the High of the day (or any interval). Because we have enough parameters available we can set up this formula in a column of its own:
Last-High(D)
This formula will subtract the High of the day from the Last of the day (D means day), and give us the difference in points. The result of this subtraction will usually be a negative value, unless the stock closes at its high, in which case the result would be 0. (Note: Some traders like to compare the Last to the high and low of the previous day, but we want to keep this simple right now, so we will stick with today's close.) You may be wondering why Last does not look like this Last(D). The reason is that Last is a snapshot data. It is non-historical, meaning there is only one value, and it is only good for now. You can’t get the last from yesterday, only for today. We could have used Close(D) instead of Last but that would have added more overhead to ErlangerQuote (historical quotes take longer than snapshot quotes).
Lets Get High
Here is how it is done. Start with a quote sheet like in the figure below. You know how to right click on the quote sheet and insert the Net and NetPct fields. How do you add the formulas? Since we want to compare Last to the High of the day lets first display the High of the day in a column. That will help us confirm the formula is working correctly.
Figure 1A Start with
this simple Quote Sheet
Figure 1B and turn it
into this with a custom formula "Last - High"
The historical High is a very simple formula. Right click on the area of the quote sheet to the right of the NetPct column and select Column->Insert Formula Column. A Formula building dialog like the one below will appear.
Formula Preferences
As you can see there are various controls for the Formula Reference dialog box. The first drop down list. Formula Name, is where you enter the name for the formula. This becomes the name that appears in the column header when you are done, so if you make it too long your column will have to be really wide to see it all. This also becomes the name of the formula when it’s saved. The names you create get automatically added to the drop down list and sorted in alphabetical order. The three buttons above the Formula Name dropdown list are for saving, loading and deleting names from this list. This is essentially your formula name management area.
The next drop down menu is a special one called the FORMULA dropdown menu. This menu contains a list of all the actual formulas that are available to ErlangerQuote listed in alphabetical order. The tiny chart icons next to some of the names indicate which of the formulas contain historical information and which do not. All these formulas are defined at our web site in the Formula Reference area.
The area under this FORMULA dropdown menu is the Formula Text Box, which is where actual results from the FORMULA dropdown menu appear and where you can also type in the actual formula with its parameters, filled in. The Operators dropdown menu is where you select what operator you might like to use, but you can also just type these in if you wish. The drop down list helps you to remember if its <= or =<.
The formula font can be changed to make reading the formula easier. Paste with Arguments does just what it says; it pastes the parameters for the formula with their symbolic names in the Formula text box. Let’s see how to use this box to build a simple formula. Once you do one it’s easy.
Figure 1C The Insert
Formula Dialog Box
Building a High Formula
Let’s go through the steps of building a High formula. With the formula dialog still open on your screen select the FORMULA dropdown and locate the High formula. See the figure below for how this looks. Once you have selected the formula, its name will appear in the dropdown field. Type High into the Name dropdown field and then click on Paste w/Arguments.
Figure 2A Step 1
Select the High Formula from the dropdown list (notice the chart icon)
After Pasting w/Arguments you should get the figure below. You will see the arguments:
High(Interval,[Ref],[Symbol])
What this means is that the High formula needs an interval entered, it could be D for daily or 60 for 60 minutes, and so on. The parameter [Ref] means what interval are you referring to, the current bar (0 or no Ref), the previous bar (-1), two bars back (-2) and so on. The brackets [ ] indicate that this is an optional parameter. It can be ignored and the formula will still work. The parameter [Symbol] allows you to enter a specific security instead of taking the one that is on the row of the formula. In other words this lets you override what is in the row or not even have a symbol in the row if you so desire. We are only interested in the High of the Day so we want a simplification of the formula like this:
High(D)
Figure 2B Step 2
Paste the Arguments for High into the Formula text box
We can eliminate the rest of the formula and enter the D for days. We should have a dialog box like the one below. Now you can click on the Save button to save this formula under the name High, and click OK
Figure 2C Step 3
Adjust and change Arguments you don't need and click OK
Building the Last Minus High Formula
Right click to the right of the last column in the quote sheet and enter this formula into the Formula text box:
Last - High(D)
Name this formula Last-High and click on the Save button to save the formula. Click Okay and you should have a new column called Last-High and some numbers showing. There is enough information in the quote sheet to confirm that the values are correct.
Figure 3 The Last -
High Formula
Formula Reference Table
Table 1 below shows the entire set of formulas in the Formula engine. The definitions of these formulas can be found on the Formula Reference list at http://www.Erlangerquote.com/refnew3/formulas.htm.
|
Table 1 ErlangerQuote
Formula Library |
||
|
|
|
|
|
Ask, A |
EarningsPerSh |
QuickRatioQtr |
|
AskExg |
EMA |
QuoteSize |
|
AskSize |
EPSGrowthRate |
ReceivablesTurnover |
|
ADX |
Exchange |
ReturnOnAssets |
|
AssetTurnover |
FinancialsDate |
ReturnOnEquity |
|
Avg200DayPrice |
Float |
RevenueGrowthRate |
|
Avg65DayVol |
GrossMargin |
RevenuePerEmployee |
|
BBBasis |
High, H |
ROC |
|
BBLower |
IndustryCode |
RSI |
|
BBUpper |
IndustryName |
RSIHAVG |
|
BestAsk, BAsk |
InsiderOwnedPct |
RSILAVG |
|
BestAskExg, BAskExg |
InsiderPurchased |
SalesPerSh |
|
BestAskSize, BAskSize |
InsiderSold |
SectorName |
|
BestBid, BBid |
InstitutionalOwnedPct |
Settle |
|
BestBidExg, BBidExg |
InventoryTurnover |
SharesOutstanding |
|
BestBidSize, BBidSize |
Last |
ShortInt |
|
Beta |
Low, L |
ShortIntPrev |
|
Bid, B |
LTDebtEquityRatioQtr |
StocD |
|
BidAskSpread |
MA, MOV |
StocK |
|
BidExg |
MACDFast, MACD |
StocRaw |
|
BidSize |
MACD1 |
Symbol |
|
BlockTrades |
MACD2 |
Tick |
|
BlockVolume |
MACDHistogram, MACDDiff |
TickVol |
|
BookValuePerSh |
MACDSlow, MACDSig |
TickVolAtAsk |
|
CashFlowPerSh |
MarketCapitalization |
TickVolAtBid |
|
Chop |
MDI |
Title |
|
ChopHigh |
MOM |
Trade |
|
ChopLow |
MoneyFlow |
Trade2 |
|
ChopTR |
NDI |
Trade3 |
|
Close, C |
Net |
Trade4 |
|
CurrentRatioQtr |
NetPct |
Trade5 |
|
DailyHigh |
OBV |
TradeExg |
|
DailyLow |
OI |
TradeInRangePct |
|
DailyOpen |
Open, O |
TradeInYearlyRange |
|
DailyOpenInterest |
OpenInterest |
TradeSize |
|
DailyVolume |
OperatingMargin |
TradeTime |
|
DaysToExpiration |
OptionExpirationCode |
UpTicks |
|
DayValue |
OptionExpirationMonth |
UpTickValue |
|
DebtEquityRatioQtr |
OptionStrikePrice |
UpTickVol |
|
Dividend |
OptionStrikePriceCode |
ValueAtAsk |
|
DividendYield |
OptionType |
ValueAtBid |
|
DnTicks |
PDI |
VolAtAsk |
|
DnTickValue |
PERatio |
VolAtBid |
|
DnTickVol |
PRCLower |
Volume, V |
|
|
PRCMid |
VWAP |
|
|
PRCUpper |
YearHi |
|
|
PretaxMargin |
YearLo |
|
|
|
Yest |
|
|
|
|
The Color Rules
Now you know how to add simple formulas to the quote sheet. Suppose you wish to use the results of these formulas to control the color of the cell it is in. For example in the case of our Last - High, the number will normally be negative, so we would like to have the background turn red in that case. How can we get the cell color to change based on a formula?
Column Properties
The figure below shows the column properties dialog box for the Last - High(D) formula. There are four tabs at the top, the default is Formula on the left.
The Colors tab is where you set up "rules" that produce designated colors based on specific values in the formula output and allows you to save them with names. In our example below we have a set of rules called Simple Green Red. The rules are created with the Rule dropdown menu (lower left) and the Value field next to it, which holds a number.
Using this dropdown you can select the phase "Greater Than or Equal To" and then enter the number 0 in the value field. Next you would set the Foreground (FG) and Background (BG) colors that you want to have appear in the cell when the value in the column is greater than or equal to 0. In this rule the foreground is black and the background green.
There is a second rule that has a white foreground and a red background when the number is less than zero. The value can be made to blink, but that is kind of annoying, so we did not turn it on. Now when you click OK this set of rules will be applied to the numbers in the formula column. In our example they will be red.
Figure 4 Color tab in Column Properties
This may not strike you as being enormously useful at first but imagine what is possible with these kinds of rules. For one thing you can create a set of colors that correspond to a range of values, sort of like "channels" that can help you visualize some pattern. These could be Fibonacci values if you are a convert to chaos theory.
Figure 4 The Last - High column is now red when the numbers are negative.
One More Formula: Price Crossover
Now that we have entered your first formula, and you are more confident, lets do one more example. Only this time let’s make the formula more useful to traders.
One of the more common kinds of indicators that traders like to use in choosing when to buy or sell a stock is known as a "moving average crossover or breakout". This is when the price of a stock crosses over or under the moving average of the same stock.
Remember we have seen that a moving average is a smoothed line that shows you the average trend of the price over a fixed period of time. When the price of a stock crosses over the moving average it is known as a bullish move when it moves UP through the moving average, and as a bearish move when the price moves DOWN though the moving average.
Look at the figure below of a candlestick chart of IBM. The moving averages are as follows:
Red = 10 day moving average line
Blue = 50 day moving average line
Magenta = 200 day moving average line
Figure 4 Three moving
averages
What you can see is a lot of interesting patterns. First for the big picture, notice that since the chart begins on the left the 200 day moving average (magenta line) is declining except that it starts trending up in mid-July. This means that over this period of 6 months the stock has generally gone down and is showing signs of recovering.
But in between IBM has been on a roller coaster for its stockowners. It’s had three swings from a low of 100 to a high of 125 or so, a 20% range. During this time notice IBM crossed its 50-day moving average (blue) 5 times. Each time it crossed the average it continued in the same direction. For example in March it penetrated the 50 and 200 day moving average around the 21st of February and peaked on the 7th of March.
Had you "gone long" (bought IBM shares) when the price crossed the 50 day moving average and sold it when it started to go down, you would have made 20% in just a few weeks. Likewise when the price crossed down through the 50 day moving average on the 12th of April and closed at $113, you could have shorted the stock and made money as it went down to $101 on the 24th.
The point is the price moving through its 50 day moving average looks like a fair indicator as to when to sell or when to buy, depending on the direction it moves. Can we make an indicator in a quote sheet that will show us when the price is above or below the moving average? Yes and that is what we will do right now with a formula.
The Moving Average Formula
Basically if we want to see the moving average in a quote sheet we can simply put the moving average formula in a column, as follows.
Right click on the quote sheet in the last column. Select Column->Insert Formula Column. The Formula Dialog box will appear. Select the formula called mov from the dropdown menu. Click on Paste w/Arguments. The following code should appear in the Formula text box:
MOV(Source,Length,Offset,Interval,[Ref],[Symbol])
This is the general format of the moving average. Its pretty much equivalent to the information you typed into the moving average dialog box when you put a moving average on a chart in the previous chapter.
Change the formula so it looks like this:
MOV(Close,50,0,D)
This makes the Source for the calculation to be the Close, the Length or number of bars to be 50 (Length), the Offset back or forward to move the calculation to be 0, and the Interval to be Daily (D). We do not need to use the Ref and Symbol parameters.
Next enter "Mov50d" in the Save Name dropdown and click the Save button to save the formula. The name helps you remember that this is a 50-day moving average formula you can use later. Click OK.
Now we should see a new column appear in the quote sheet to the right of the Last and it should display the 50-day moving average calculation for each stock.
Next we need to know if the price is above or below the moving average. Does that sound like the same question we asked earlier about the close of the Nasdaq and the high of the day? Sure we just subtract the Close from the Moving average and we know from the sign of the results if the price is above or below the stock. Here is the formula:
Close(D) - MOV(Close,50,0,D)
Go ahead and add this new formula column to the quote sheet right next to the one you just made called Mov50d. Name this new one Close - Mov50d.
(We resized the row to make the text wrap in the column and fit better. To change row size go to the QuoteSheet dropdown menu and uncheck Quotesheet->Grids->Lock Row Height. Now you can resize the row and the text will automatically wrap so the column will be narrower and save space in your quote sheet window). You should see a new column appear as shown in the figure.
Figure 5 Adding the
50 Day Moving Average Breakout formula column
More Thoughts About Formulas
But lets think about this some more. What good is it to know
if the price is above the 50- day
moving average if we do not know how long it has been above it? Did this event
just happen today, in which case I am interested in buying the stock or did it
happen many days ago in which case maybe no? To find the answer I would have to
study the chart. What I really want the quote sheet to do is alert me when the
moving average crossover occurs.
Your First Quote Sheet Alert
Okay so we want to see an alert when the stock price moves from being below the moving average to above it (or vice versa). Lets break this into two steps:
1. Did the price drop below the moving average today?
2. Was the price above the moving average yesterday?
If these two things are both true then we have a stock that just moved downward from yesterday to today through its 50 day moving average.
Lets first look at the formula for showing an alert when the price is ABOVE the moving average:
Close(D) - MOV(Close,50,0,D) > 0
All we did is take our last formula and add the operator for "greater than or equal to" (>= 0) at the end of the equation. What this does is make the formula end up being either True or False, rather than just a number. That is all an alert is, a formula that evaluates to a True or False condition. So for this equation to be true the Close(D) daily price has to be greater than the 50 day moving average. If the Close(D) is less than the moving average this equation will be False.
Go ahead and add a formula column to the quote sheet after the last one you added with this equation and name it "Close - Mov10d" (include the spaces). Enter a collection of stock symbols so that you have more data to play with, such as AOL, IBM, CSCO, and AAPL. You can also gain more width for your quote sheet, by hiding columns. This is done by right clicking on the column and selecting Column->Hide Column. We hid the High Column since we won't be using it in this exercise. (To hide a column, right-click that column and select Column->Hide Column(s).
Figure 6 Adding the
Close - Mov50d Formula Column
When you are done you should see a new column with the blue cells that say True and white cells that say False. Look at the numbers in the Close - Mov50d column and this will make sense to you. In our figure the first four rows of symbols had a Close that was above the 50-day moving average and so the number was positive and that evaluates to True. In the case of the last two rows, the Last was less than the 50-day moving average the cell is negative, which is false for our equation. We now have half of our alert system working. It is possible to change the colors and the text that appears here so you can have your quote sheet formulas tell your brain an easy message to process. For example, instead of the color blue and the word true, you could have the background color green (implying a possible long position) and the text could say, “Above 50d MA” or simply, “Above”.
The Rest of the Equation
Class is not quite over. We need to know if our stock was below the moving average yesterday to complete the alert. Because if the price was below the moving average on the previous day, and today we are above the moving average, then Eureka—we have found the magic combination—and we want a True alert. We will do the opposite for going short. To make these two alerts stand out as different, one will display Above and the other Below when they are true and nothing when they are False.
However, do we set up a formula that handles the previous day? Well we want to know that the previous day price was below the moving average. Remember the parameters for the moving average:
MOV(Source,Length,Offset,Interval,[Ref],[Symbol])
The important parameter here is [Ref], which allows us, to Reference different Intervals other than the current Interval. For example, a negative number for Ref refers to a previous bar in the interval. If you are using Daily for the Interval then -1 would refer to the previous day, -2 to two days back and so on. So we can write:
Close(D,-1) - MOV(Close,50,-1,D) < 0
This formula says "subtract from the close of the previous day, the 50 day moving average of the previous day, and if the result is less than zero, make it evaluate to True, otherwise make it False". By now this should be making sense to you. Go ahead and enter this in your quote sheet and name it "Close - mov50d < 0 Prev". You can see the formula in the figure below.
Figure 7 Adding the
"Close - Mov50d < 0 Prev" Alert Formula Column
Okay we are still not home but close. We need to now compare to the two close - moving average formulas and make sure they are both true. Here is how that formula should look:
For reference, the first and second formulas are listed here:
Close(D) - MOV(Close,50,0,D) > 0
Close(D,-1) - MOV(Close,50,-1,D) < 0
The first formula will tell you that the close is above the moving average. The second formula will tell if yesterday’s close was below the moving average. If we could put them together, we would have a formula that answers both questions in one step.
This final formula will give us a true condition when both of these two formulas are True. How do we do this? We use the magic AND operator. AND is designed for hooking equations together. You will use it a lot when you write your own formulas.
The two formulas are now connected by the AND operator as shown below. The formula is encased by parenthesis to indicate that the two formulas tied by the AND are complete.
( Close(D) - MOV(Close,50,0,D) > 0 ) AND (Close(D,-1) - MOV(Close,50,-1,D) < 0 )
The AND operator means what it says, BOTH conditions must be true for the overall condition to evaluate to True, if either one is False the condition is False. So go ahead and enter this final formula in a quote sheet and call it "50 day Crossover Up". You can see in the figure below that the column shows us that there are NO stocks in the list that meet the 50day Crossover criteria and they are all reporting False. How can we find some stocks that do meet these requirements? Have you heard of screening or scanning?
Figure 7 Adding the 50-Day Crossover Up Alert Formula Column
Your First Screening 
The best way to find what stocks meet a certain criteria is to scan a list and filter out the ones you want with a formula. This is called screening, or scanning. Suppose you wanted to know what stocks on the S&P 500 have had 50-day Crossovers Up, in other words met the criteria of the breakout formula you just made?
Screening by Importing
Create a new quote sheet. Add a column with the 50-day crossover formula we just made. Right click and Import an SP500.sym file to the quote sheet. The column will fill with True or False results as the data is loaded from the Quote.com server and calculated. To have the symbols sort dynamically, turn off Sort Lock and Turn on AutoSort. Now click on the header of 50 day Crossover Up column. All the stocks with True will sort to the top. With this simple method using a quotesheet, the information does not auto update every X minutes. If you wanted to find stocks during the day that were crossing their 50-day moving average, you would have to re-import that .sym file repeatedly throughout the day. There is a way to do this automatically and that is with the scanner functions.
Screening with the Scanner
Click on the Scanner icon, the one that looks like a blue
bar code. 
This will bring up the Scanning Manager
dialog box below.
List to Scan
At the bottom of the dialog box is the area where you select what Lists you wish to scan. Here you can select any list that is showing, or use the List Editor to find a list on the disk and add it to the List to Scan area. To get a list to scan click on the List Editor button. A dialog will appear with an Add and Delete button. Click Add and a browser will appear. Use it to find the file SP500.sym in your ErlangerQuote main directory. Click OK until you end up back at this screen. You should see the path for the list in the window called List to Scan.
Screening Formula
Here is where you specify your formula for screening. If the formula exists on the Name dropdown list you can Load it. You can also save and delete it from this dialog. If you want to you can type in a formula, or you can use the Formula Builder… button to make a new formula using the same formula dialog you use to make column formulas.
Start Scan
You can then ask the Scanner to create a quote sheet list with just those stocks in the list that meet the criteria in your formula. Since we saved our formula for the 50-day crossover, lets load it in and use it to make a filter. In the Name filed dropdown menu select 50day Crossover Up. Then click Load to enter that formula in the Formula text box.
Check the box Rescan list every, and make sure 10 is in the minutes field. Now select the List by clicking on it and click on Start Scan.
Figure 8 Scanning Manager
The dialog box will be replaced with what looks like a quote sheet with one column. You can see in the figure that there is a small horizontal progress bar that shows you how many symbols have been processed and what percent is done. In our figure below we embellished the scan sheet by adding three columns. We added a Last, a formula for the 50-day moving average (50MOV) and a formula that subtracts the 50MOV from the Close (same as last), and shows us the difference, or how above the 50 day MA the Last closed.
This is essentially the stocks from the SP500.sym file, which meet the criteria in our 50 day crossover up formula. Note that there is a cancel button at the upper right corner of the scan sheet. When we are done we can add additional columns to this scan sheet and we will still be able to set it to "rescan" the list every N minutes (which can't be done with regular quote sheets).
Figure 9 Scanned List in progress, note the progress bar
Out of 500 stocks in the SP500.sym file ErlangerQuote's scanner found 7 symbols that had prices that crossed over their 50 day moving average going up. The list is not sorted in any particular order but can be if the AutoSort option is enabled (under View->Sort). Adobe rose 6.39 dollars above its 50-day moving average of 126.24:
.
Figure 10 Scanning done
No Data
Sometimes a list that you use to scan with might contain old data. If a symbol returns no data, it could be that the company has been delisted due to several reasons. These reasons include acquisition by another company or bankruptcy. Whatever the case, symbols that fit into this category can be vexing if you are attempting to scan a large list of stocks. ErlangerQuote displays a yellow warning button in the header area at the end of the scan if there are symbols that don’t return any valid data. This can be seen in the figure below where some of the symbols did not appear.
Clicking on the yellow button will bring up a dialog box shown below. This explains how you may have arrived at this condition, for example pointing out that some operations, such as trying to use "ask" in a scan for the OEX which has no “Last”, will cause problems. If you click on Yes the bad symbols will be opened automatically in the default quote sheet.
It Is Done with Multitasking Mirrors
However this is not enough information to rush out and place a limit order. At this point you would use other filters, studies, and plugins to further narrow the candidates and to confirm that the moving average "signal" is a valid one. Keep in mind that the moving average is a lagging indicator that does not work well in non-trending markets.
Figure 11 A stock
with a Crossover Breakout from our list
The beauty of scanning is that you can have screened lists being generated for you on a continual basis, and because ErlangerQuote is a threaded multitasking application, you can, at the same time, examine the charts of these candidates as they are generated!
Coloring Formula Cells
We skimmed over the power of the formula columns. One of the most powerful features is the ability to color the individual cells of a formula column using the Colors tab in the Column Properties dialog box. Below is a quote sheet we created with a MACDHistogram formula inserted in the column after the stock's Title. This MACDHistogram will help us quickly see if the trend is positive, though there are more refinements required to know the whole picture.
Figure 12 The MACDHistogram formula column with custom
colors
Note that the column is showing 3 different colors: red, blue, and yellow. We set this up using the Colors tab of the formula dialog box. We'll show you the rules in a moment.
Take a look at the Column Properties dialog box for a MACDHistogram in the figure below. The MACDHistogram formula is entered in the dialog box from the Fields dropdown list and then its parameters are entered. Here we selected 12, 26,9 just like the default for the MACD in the chart studies.
It is important to note that the formula does NOT evaluate to a True or False condition. This means this is not an alert. Had it been an alert there would have been a Boolean operator and an equal sign in the formula and we would have been interested in the Alerts tab of this dialog. The green shading in the dialog box represents that there is a relationship between the formula and the Colors tab.
Figure 13 The Formula Column Properties dialog box
showing a FORMULA