Solved

Conditional Formatting with Excel - Part 5

Posted on 2011-03-09
19
221 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
  • 10
  • 4
  • 3
  • +2
19 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
Where is the program "esrtd" located?
0
 

Author Comment

by:cpatte7372
Comment Utility
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
 

Author Comment

by:cpatte7372
Comment Utility
Experts,

Any thoughts?

Cheers
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:cpatte7372
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
>>> (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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You are welcome :)

Sid
0
 

Author Closing Comment

by:cpatte7372
Comment Utility
Brilliant .....
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now