Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Conditional Formatting with Excel - Part 5

Posted on 2011-03-09
19
Medium Priority
?
238 Views
Last Modified: 2012-05-11
Hello Experts,

Someone familiar with trading stocks on the stock market will feel at home with this request. However, it really requires someone proficient with Excel.

If you're willing to help you will need to refer to the following link.

http://blog.kilotrader.com/2010/05/backtest-hammer.html

In the attached spreadsheet you will see each symbol with a corresponding open, high, low, close.

The symbol represents a company, e.g msft is microsoft etc... the open, high, low , close, simply means the price at which it opened, how high it went, how low it went and where it closed.

There is certain pattern that market traders use in prices called the Hammer. This is where you'll need to view the link to understand what a Hammer looks like.

I have programmed a conditional formula that is taken from the link. I tweaked it to correspond with my spreadsheet. However, either I've programmed it incorrectly or the actual code is wrong.

I would truly grateful if an Expert could help.

If you need further explanation please let me know.

Cheers


Hammer.xlsm
0
Comment
Question by:cpatte7372
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 3
  • +2
19 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086546
I am not an expert with this but I am willing to help. In this process, I will learn as well :)

To start with, this is that the Excel help file says.

The RTD COM automation add-in must be created and registered on a local computer. If you haven't installed a real-time data server, you will get an error message in a cell when you try to use the RTD function.

So could it be this reason that you are getting the error.? I am in the meantime checking the link that you gave.

Sid
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35086708
Where is the program "esrtd" located?
0
 

Author Comment

by:cpatte7372
ID: 35087068
Guys,

Can't thank you enough for trying to help me with this.

Please, please ignore "esrtd". I thought I had taken it out. It's a link to datafeed, similar to reuters.

Please just focus on the numbers. If you can get the formula to work that will suffice.

Again, trust me, RTD functions, esrtd, has no bearing on what we're trying to achieve.

If you need further explanation let me know, but whatever you try to do won't be hampered without the extra add-ins.

Again, I know this is tricky one, thanks for taking a stab at it...

Cheers

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:cpatte7372
ID: 35089088
Experts,

Any thoughts?

Cheers
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35089517
Hello Carlton,

To which formula are you referring? In Bullfish Engulfing worksheet you have this formula in CF

=AND(F2<C2,J2<G2,G2<C2,J2<F2,N2<K2,K2<G2,N2<J2,O2<N2,Q2<M2,R2>O2,R2>K2,P2>L2,V2>S2,V2>R2,S2>O2)

Is that the one?

When I open the workbook all the entries in those cell are #N/A so I can't see what's highlighted if anything, can you attach the workbook with "hard-coded" values in those cells?

I think I can see that F2<C2, J2<G2 and N2< K2 represent 3 days where prices are down....then R2>O2 represents price UP on day four and V2>S2 is UP again on day 5...but you have J2<F2 as well, won't that be automatic if J2<G2?

...and especially O2<N2. Won't the last price one day be the same as Open on the next, so wouldn't O2 = N2?

Perhaps you can explain the logic that you are trying to put together.....

regards, barry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35089532
I'd like to provide some thoughts.  However, a couple of the spreadsheets with the RTD calls have #VALUES so Cannot examine values.

When I look at conditional formatting on any of the pages, I get nill.

Put my nose in it - what sheet/cell do I go to to see your conditional formatting I can help you with :)

Cheers,

Dave
0
 

Author Comment

by:cpatte7372
ID: 35089590
Ahhhhhhhh....

Now I understand why I was being asked about esrtd, and RTD. I should have deleted all the other worksheets including 'bullish engulfing' and just posted 'four lows five high'.

I'm really sorry about that because it has probably wasted experts time. Please ignore all other worksheets. Just so you know in the other worksheets where you esrtd it's link to a market data feed that pumps stock prices into my worksheet. As you can imagine you won't get any data because because spreadsheet needs to sit on my computer and you would have to pay money for it.

I provided a snaphot of the spreadsheet with data in 'four lows five high' not realising that I had forgotten to remove the other worksheets. This probably explains why I haven't heard from you guys.

Sorry for the confusion.

Anyway, Im writing this from my iPad in bed. I'm probably going to nod off in a bit as im really tired If you need further clarification let me know.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 35090003
Dave/experts

I've caused a lot of confusion with the other sheets. I would ask you to forget about all sheets apart from 'four lows five high'

 Cheers
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35090789
cpatte7372: I don't see any conditional formatting in that as well. What exactly do you want to be done in that worksheet?

Sid
0
 

Author Comment

by:cpatte7372
ID: 35093760
Sid/Experts,

Once again, please forgive all the confusion. I submitted this request very late last night and wasn't paying attention.

I have attached the spreadsheet again, this time without the additional worksheets and with the the conditional formatting with the formula suggested on from the following link:

http://blog.kilotrader.com/2010/05/backtest-hammer.html

As I mentioned at the begining of this request, someone with a background in trading stock markets may come to grips with this request a lot quicker, however as long as an expert understands excel that will suffice.

If you don't have any idea about trading the stock you WILL need to take a look at the link.

Basically, I have written a condition in the spreadsheet with the formula suggested from the link, however when I put the formula to the test it doesn't seem to work. I was just simply wondering if someone could take a look at how I've written the conditional formatting formula and let me know if it appear's ok to you.

Again, I know most of you guys aren't traders so with me asking you to take a look at the link and getting to grips with a little about trading is going beyond the call of duty. Which is why I really appreicate any help you can provide. I explained at the start of this request what I aim to achieve and the link explains it in more detail.

So please take a look and see if the formula from the link will work in the conditional formatting I have placed in the spreadsheet.

Cheers

Carlton
Hammer.xlsm
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35094774
I checked the attached spreadsheet and the formula seems exactly like the link you provided. All the cells (for the first column) that meet the criteria are colored in red.
Where exactly is the problem?
I did the formula by parts (one column has the 10% of the difference, another column has "high - 10% of the diff", and a final column evaluating close with the whole operation, and whenever close is less then the value the first column is colored.

Am I understanding your question here? I don't think so...
0
 

Author Comment

by:cpatte7372
ID: 35095582
LIONKING/Experts,

I have the code I need:

I need a conditional format that will highlight the cell in column A in the attachment when the following condition appears:

Close > Open), (Close - Open) > (High - Close) and (Open - Low) > (Close - Open)*1.5

I can't tell you how much trouble I've gone to get this formula :-)

Anyway, if you guys/girls can help put the above condition into a format in excel I would be soooooooo appreciative.

Cheers

Carlton
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35097238
>>> (Close > Open), (Close - Open) > (High - Close) and (Open - Low) > (Close - Open)*1.5

If I convert the above to a formula then it is similar to this

=AND(F2>C2,(F2-C2)>(D2-F2),(C2-E2)>(F2-C2)*1.5)

When I actually evaluate it by putting it in the cells then I get "False" for every condition.

Could you confirm if that is what you want?

Sid
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35098133
I think Sid has it... You just need to add a comma before the last parenthesis and add your other condition...
0
 

Author Comment

by:cpatte7372
ID: 35098647
Wicked,

I'm going to check it out... If it works I'll buy you drink anytime you're in London.
0
 

Author Comment

by:cpatte7372
ID: 35100614
Sid,

You've nailed it. Can't thank you enough. I'm going to make loads of money!

Cheers mate.....
0
 

Author Comment

by:cpatte7372
ID: 35102375
Sid,

I guess I should mention why you get a "False" for each condition in the sample. It's because getting a positive is quite rare in the trading world. When a trader sees one, if he knows how to play the Hammer he has set him/her self for profits on the stock market.

Once again, thank you very much

Carlton
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35102515
You are welcome :)

Sid
0
 

Author Closing Comment

by:cpatte7372
ID: 35306090
Brilliant .....
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question