Here’s another example of how to make useful custom formulas with Trade-Ideas.  

Recently a customer was looking at  our Cash/Debt filter .  He sorted by that, so he could quickly find the strong stocks.  But there was a problem.  He wanted to see the stocks with no debt on the top.

When our computers see something divided by 0 in a column, Trade-Ideas displays a blank on your screen.  When you try to sort or filter on a something divided by 0, we throw out the stock completely, so you don’t see it.

That’s the default.  If you want something else, try writing a custom formula.  Or, do like this customer did, and ask us to do it for you.

The first solution I offered was the flip the formula.  Instead of displaying each company’s cash divided by it’s debt, display debt divided by cash.  In the formula editor type:  [Debt]/[Cash]  Then change the sort order, so the smallest numbers are on top.

I liked the simplicity of that, but I knew I could do better.  The biggest problem was that a lot of companies have no debt.  These all had a value of exactly 0 for our new filter, so those companies weren’t listed in any particular order. 

The customer was looking for strong stocks, the companies with the most cash should be listed above the companies with less cash.

I did a little more research.  A lot of companies have no debt.  This shows up as 0 in our system.  If I ignore those companies for a moment, the strongest companies have a cash/debt ratio of about 880 to 1.

I decided to make the companies with no debt show up with a ratio of about 999 to 1.  That would put them above the companies with a small amount of debt.  Then I could adjust that number so it was slightly higher for stocks with more cash, and slightly lower for companies with less cash.  Here’s the formula.

Here’s the result.

Notice the columns.  From left to right:

• The stock symbol,
• The amount of cash, i.e. [Cash] or ,
• The built in cash/debt ratio, i.e. [CashDebt] or ,
• My first attempt at the new cash/debt ratio (I always start with something simpler and build up to the more complicated formulas),
• The smart cash debt ratio, which was our original goal,
• And the amount of debt, i.e. [Debt] or .

Also, notice that the column heading use words, rather than icons.  Usually I prefer the icons.  But the icons for custom filters aren’t as nice and are harder to read.  So when I have a lot of custom formulas I sometimes use the right-click menu to turn on “Text Headers”.

There are a lot of companies out there with no debt.  For the sake of my test, I didn’t use any filters.  So I requested 1,000 stocks.  (The default is 100.)  And I scrolled way down, to see this.  This is what I’d expect the customer to see, after he added his normal filters.

Displaying Something Different

I discussed this some with another support person here at Trade-Ideas.  The smart cash/debt filter, shown above, works well, but it might be hard to explain to some users.  So we came up with a different approach.  Here’s another filter that is similar, but slightly simpler.

If you try to display the results, they will look a little strange.  I don’t know how I would explain that value to a customer.  But I don’t have to.  I call this the “sortable cash/debt ratio”.   I created a window for the customer which displayed our normal fields, like cash, debt, and cash/debt.  But I sorted by this value.  You don’t have to display the sort field as a column.

I usually display the sort field for myself.  I like to see the plumbing.  I like to see how things work.  But the customer doesn’t have to.  He can see the pretty and easy to read stuff, and just trust me for the rest.  It’s up to him.  And you can choose for yourself which way you prefer.

“Just one more thing.”  I  hear that a lot.  I know I’m doing a good job when people ask for more.  If they like what I gave them the first time, they always want more.

This customer wants to see the very strong stocks next to the very weak stocks.  He wants to see them in the same window.

We get similar requests a lot.  We usually call this “book ends.”  If you’re dealing with negative and positive numbers, like the change from the close, I’d suggest that you look at the absolute value function, abs().  But this example is a little more exciting, so I had to use an if() statement.  Here’s the formula.

Notice that I didn’t worry about cash being 0.  I could have used the same trick.  But instead, I looked it up in a top list window.  There are no stocks on the major exchanges with 0 cash. 

So, what’s the best answer?  We don’t know yet.  We’ve presented the customer with several options.  He can try them all.  He can choose the one that’s best for him.  Or we can keep making small tweaks until he’s satisfied.  That’s the beauty of the formula editor.  You can try a lot of things.

This example focused on two particular data points, cash and debt.  But these tricks will work on any data.  Any time you want to divide by 0, you can use similar tricks.  And if you want to do something else, let us know.  The formula editor is filled with possibilities.