excel users

i am not that good using excell maybe someone can help me... i am running a DDE link and the data for the stocks last price is comming in at one cell and is automaticaly updating every trade.. my question is,...is there a function in excel to record the high and low out of the data with in that one cell? would it be possible to make the back ground change color or make a possible alert at new highs?or pre slected price points?? any information is apperciated and thanks in advance...any other excel tips please share the wealth. Rocky

 

to change color you can do conditional formatting.

 

Robert's tip is all you need for the color question, that one will nail it.

Here are two references for you that are "excel"- lent , yuk yuk, in my opinion, one you can go to right now, the other is a book. The website MrExcel.com is absolutely awesome. Not only are there archived questions and answers, but on the site you can post your questions and any number of people will reply with answers and code. There are even guys there that work for MrExcel that are awesome and they are really helpful.

The book is Using Microsoft Excel 2000 by QUE. Simple, has everything with great examples.

 

I also highly recommend "Microsoft Excel 2000 Formulas", by John Walkenbach.

It's a well-written book. After reading it, you can have Excel doing tricks you never thought possible.

 

You can color format the one cell if it is higher or lower than an absolute number, but to compare it to itself requires creating another variable somewhere that stores the variable in the cell as a static (one that isn't live) variable, so you can compare the two. I haven't figured out how to do it in Excel, but I know it is doable. However, it will require a Macro or VBA thingy, unless I am mistaken, and, if I am, I hope someone will tell me.

Let me point you to <a href="http://216.92.17.166/board/index.php">this Mr. Excel page</a> where you can ask the question of Excel programmers (please keep us informed). If you go <a href="http://www.mrexcel.com/consultingservices.shtml#stockmarket">here,</a> he touts something he has already written that you might be able to buy at a reasonable price.

(I've been to Walkenbach's site and it is amazing so his book probably is, too).

 

Moderator, this thread should probably go in Software.

You'll very likely need to learn Visual Basic for Applications (VBA) in order to do this. Once you get the hang of it, it's really quite straightforward. Each DDE update is an event in Excel, which is why sheets with lots of DDE cells can really tax your computer's processor at the market open. You can use that event to trigger some code in VBA which writes each new data item into a list. Definitely check out the sites that others have recommended and get a book or two on Excel VBA.

In that case also, VBA will open up a whole world of possibilities.

Here's an example:
Let say your cell in question is B4. You want to highlight that cell based on its being greater than some value, say 64.53.

The code's as follows:

Sub ColorCell()

If Range("B4").Value > 64.53 Then
Range("B4").Interior.ColorIndex = 34 ' This is cyan
Else
Range("B4").Interior.ColorIndex = xlNone
End If

End Sub

Now you can call this from a simple routine that calls this subroutine every 1, 5, 10, or any number of seconds or minutes, and updates that cell's color. You can do the same thing with a *.wav sound file as well.

 

Actually, you don't need VBA or macros to accomplish your task. You can do it using a formula that employs a circular reference.

First you must go to Tools > Options > Calculation, and make sure the the Iteration box is checked.

Now you can write a formula that references the cell itself, comparing the existing value to some new potential value. See Walkenbach's book for detailed explanation and actual examples (which are included on the CD-ROM that comes with the book).

 

ill give this a try...how does that affect other things you have going in in Ecxel?

if the data is coming into b2, you can't say if b2>b2. You have to store a past value of b2 somewhere, in order to begin logging higher values. Can that be done in the spread sheet? Every reference to b2 will update with b2. You need memory for this, or a macro that stores the value of b2 only (so that it doesn't automatically update) in another cell every so often.

 

I usually have about 50 stocks that constantly update Last, High, and Low prices. I have conditional formatting on about 6 columns. No problems at all.

As for logging the high of the day, every trading platform DDE link that I've come across so far has the ability to link in not only the last price, but High, Low, plus other data points. You can create a column for the High values to use as a reference. If you don't want to see this column (or don't have the screen space), then you can just hide the column.

Here's an example of using circular references to create a trailing stop based on a defined stop size. (I've simplified it a bit from the production version to illustrate the key concept).

IF((Last - StopSize) > TrailingStop,(Last - StopSize),TrailingStop)