Calculating Fibonacci numbers

[vba317]

I have been searching the web for about three weeks for source code on determining fibonacci ratios for excel vba. So I could use any help if possible. The first step is to determine what the flow of the numbers are in column D. Example:
Column C Column D Column G
High Low
28.19 27.82
28.58 27.88
27.90 27.68 A
28.88 28.29
28.60 28.41
30.17 28.96
31.44 29.61
33.96 31.61
34.36 32.44
34.20 32.55
32.91 31.29
34.65 33.05
33.83 32.75
35.27 33.48
36.86 34.82

So what I would like (if possible) is when the number 27.68 went up instead of down I would like the letter A to be inserted in the G column. Any help would be appreciated

Tom

 

[PHV]

No VBA code needed.
Select G3:G16 and put this formula in G3:
=IF(AND(D3<=D2,D3<=D4),"A","")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I tried to simplify my question, but it seems I may have simplified too much. I do need column G to say A for the first change in direction. But I also need the letter C to be inserted in the next change in direction. For my whole list. I also need the letters B and D to be inserted in column G for direction changes in column C. So at the end I should see :
Note: The reason I wanted this in a macro is because I am trying to calculate these directions on 20 sheets and each sheet has about 600 lines in it, every day.

Column C Column D Column G
High Low

28.19 27.82
28.58 27.88
27.90 27.68 A
28.88 28.29
28.60 28.41
30.17 28.96
31.44 29.61
33.96 31.61
34.36 32.44 B
34.20 32.55
32.91 31.29 C
34.65 33.05
33.83 32.75
35.27 33.48
36.86 34.82 D
35.21 34.02

I hope this clarifies my issue.

 

[TonyJollans]

I must say this is confusing and nothing, AFAICT, to do with Fibonacci numbers.

By change of direction what you seem to mean is that the value is less than (or equal?) to the value above - and you want the first two such situations in each of columns C and D identified in column G - and you hope (or do you know?) that the situations in column C and D always arise on different rows.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[vba317]

Thank you for your patience. After reading Tony's response I will attempt to clarify:

The A level only looks in the low column. The A row will be the last row that the value is going down in direction. You see that it is marked in the row that the value is 27.68. The next row the value went up.

The B level only looks in the high column. The calculation would start in the rows 28.88 to 34.36 the next row 34.20 is lower.

The C level would than start to be calculated. The value is only in the low column. The calculation would end when the value goes up. The value got identified when the value changed from 31.29 and the next row went up 10 33.05.

The D level would start to be calulated . The value would only be in the high column. When the direction changes to go lower the D level would be calculated. As I wrote this I realized I picked a bad example. The D level should be defined as 33.83.

After the D level is defined the next level would be an A level and it would go to B ,C and D, over and over.

The fibonacci numbers come once the levels are defined. For the A level and the C level another column would be calulated, labeled Fibonacci Ratios, the formula would be =(Previous High- number of level)/(Previous high-Previous low)

The B level and the D level the fib ratio column would be = (Number of level- Previous low)/(Previous high - Prevoius low)


I hope this clarifies my question.

 

[fumei]

Tony, these do in fact have something to do with Fibonacci numbers (although to my mind it is a almost a voodoo connection), but as the OP mentions, it really is Fibonacci ratios that are the point.

These are terms used in technical analysis that refers to the likelihood that a financial asset's price will retrace a large portion of an original move and find support or resistance at the key Fibonacci levels before it continues in the original direction.

Thus "Fibonacci Retracement".

This is why the OP wants to know the direction (up/down).

Gerry

 

[TonyJollans]

Thank you, Gerry.

In the absence of anybody else, I guess it's down to me!

The A row, the last row the low column goes down, is the first row where the previous row is higher and the next row is higher (or equal). I can see that.

Starting in the row after the A row (value 28.88), the high column goes down immediately (to 28.60). Why is that not the B row?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[vba317]

I know this complicates the issue a little. When comparing the high value if the value goes down less than 2% that is still considered to be going up.

Tom

 

[fumei]

When comparing the high value if the value goes down less than 2% that is still considered to be going up."

Like I said, it seems like voodoo to me.

Well if anyone can figure how to do it (regardless of the why), Tony and Skip should be able to. It is way beyond me.

Gerry

 

[TonyJollans]

Is it also the case that a rise of less than 2% is considered a fall?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

Probably. And a change of 1% either way is considered no change at all.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[TonyJollans]

LOL!

And what about several consecutive falls of, say, 1% each?

In order to code anything, there has to be a proper spec.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website