Excel Code 1

[Mark2Aus]

Hi everyone. Happy New Year!!!

I need to do some tests on a series of prices.
Prices are entered manually (+/- 100 prices per day) in a single Excel row.
For every new price I need to check whether it is at least 10 points above o below the latest price entered. If it is, I have to check the next time price will cross the latest peak/trough alongside that same direction. If so, in a reserved number of columns (after the end of the area reserved for entering the prices) I will write to "Buy" or "Sell" and the crossing price.
Probably it will become clearer below:

Row 5: 123 125 123 126 124 134 131 136 ...

So, I will have to check when the price is larger (or smaller) than the previous price. In the example above, the first of such price of interest is 124 (as the next price is least 10 points higher than 124). Therefore, the next time prices will cross upwards 134, the next peak after 124 has occurred (because after 134 has been recorded, price fell to 131 making 134 a peak), I will write "Buy" and the correspondent crossing price, which will be 135.

The same should happen to prices which are falling. Example:

Row 6: 123 125 123 126 124 129 119 121 115

So, as prices fell from 129 to 119 (10 points), 129 becomes my price of interest and the next time prices cross 119 (the next trough after 129 has occurred because after 119, prices rose to 121, leaving 119 as a trough), I will write "Sell" and the correspondent crossing price, which will be 118.

I hope this has become clear.
As I have no expertise in writing VBA, I am a techical user of Excel, I dont't know how to do it.
Can anyone help me?

Thanks

MarkAus

 

[Mark2Aus]

Hi Skip. Thank you very much for all your help.

By the end, I could not do the test on the prices as there are more than 5 years of prices which were entered manually.
I tried to create a new spreadsheet, copy and paste the code and then copy all the data but it did not work.
Also, the code shows me repetitive signals Buy/Sell and this distort the test.
Also, each signal shows something like Buy:124 and for me to find out whether this was profitable or not, I would need to compare it to the next contrary signal (say Sell:121) on the same row to obtain the profit or loss (if it was bought for 124 and sold for 121, a loss of 3 points occurred) and I don't know how to do that calculation using VBA.
In fact, when you don't know how to write VBA, the simplest of things become so difficult as a single character might invalidate everything. When you told me to go and look at the functions Split and Instr, I went and had a good look at the functions, but I could not grasp what to do with them and how to use them.
I am a financial manager (a bit rusty already with the new technology) and user of Excel. I have never had any experience writing codes and even don't know how to start. I can use Excel and create some formulas, involving simple functions such as IF, INDIRECT, SUMPRODUCT, etc.

Anyway, I would like to thank you very much for all your trouble in the last few days. It was all worthwhile. I am planning to start studying how to write VBA codes in the future. It will help me to understand its possibilities.

MarkAus

 

[SkipVought]

Mark,

This is not...

Tek-get-a-system-wirtten-for-you-for-nothing.

Its Tek-Tips.

Hopefully, you got some good VBA tips. One great tip, IMHO, is the suggestion to try some coding yourslf.

I'd wager a guess that most of the "experts" here, do not have a degree in Information Technology. I happen to have a BSEE, 1966. NO computer programming at college. I used a slide rule.

Your requirement seemed to evolve.

"For every new price I need to check ..."

to

"I could not do the test on the prices as there are more than 5 years of prices which were entered manually."

If you do not want to code, I'd suggest finding one, if there is one, at your place of employment or hiring an expert to do it for you.

I took the bait, because it seemed like an intriguing problem, and to that extent, it was an enjoyable challenge and respite from my normal pursuits at the aircraft plant.

You really were not looking for a TIP, were you? But that's okay!

Skip,

_[red][/red]