Any EXCEL Gurus?

[StockBaron]

ColA is a a column of stock SYMBOLS that I want to monitor using a DDE link with IQLINK (DTN IQ Feed).

Columns B thru ZZZZZ are the specific data items I want to monitor (Bid, Ask, Volume etc. etc.)

At present I have to hard-code each cell along the lines of "=IQLink|msft !Volume" manually entering the symbol (msft in this case) in column A into every other column's specific forumla in that row.

It would be nice to have columns B onward pick it up automatically.

I've tried CONCATENEATE, INDIRECT, VALUE etc...with no luck,

Anyone have an idea how to do this?

 

[TWI]

How about following
=IQLink|&"cell ref"&!Volume
where "cell ref" refers to a cell with msft or other symbol written into it.

 

[Tun]

If I was you I'd be using Excel VBA Macros for this. Shout if I'm talking jargon I've no idea what you do/don't know.

The answer below won't be what you need but hopefully it's a step in the right direction. If I get more details about what's required I can give a more accurate answer.

For the time being tho, open a blank spreadsheet, stick a few dummy stock name values from A1 to A5 or so

Then run the below macro

Sub InsertSymbol()
'
' InsertSymbol Macro
' Macro recorded 29/03/2004 by Tun
'
Dim strActiveRow As Integer
Const cstrActiveCol As String = "A"
Dim strStockName As String

strActiveRow = 1

Do While Cells(strActiveRow, cstrActiveCol).Value <> ""
strStockName = Cells(strActiveRow, cstrActiveCol).Value

Cells(strActiveRow, "B").Value = "'=IQLink|" + strStockName + " !Volume"
Cells(strActiveRow, "C").Value = "'=IQLink|" + strStockName + " !Bid"
Cells(strActiveRow, "D").Value = "'=IQLink|" + strStockName + " !Ask"

strActiveRow = strActiveRow + 1
Loop

End Sub

It should populate columns for each row with info similar to what you need.

Hope this gets you started in the right direction.

 

[StockBaron]

How about following
=IQLink|&"cell ref"&!Volume
where "cell ref" refers to a cell with msft or other symbol written into it.

Did you actually test this before you posted it?

 

[RedE]

IQLink Macro

I was bored, so I wrote this macro for you.
It took 3 minutes, so don't worry about compensation!

The code is in the attached Excel file.
and it is:


Sub InsertIQLink()
Dim rngCell As Range
Dim rngSel As Range

Set rngSel = Selection
For Each rngCell In rngSel.Cells
With rngCell
.Offset(0, 1).Formula = "=IQLink|" & .Value & "!Bid"
.Offset(0, 2).Formula = "=IQLink|" & .Value & "!Ask"
.Offset(0, 3).Formula = "=IQLink|" & .Value & "!Last"
.Offset(0, 4).Formula = "=IQLink|" & .Value & "!Volume"
End With
Next rngCell
End Sub

It has been tested, but I don't have DTN anymore, so you'll have to test the DDE integrity.

Always beware macro's which are recorded - they ALWAYS break! Never reference cells as Tun has done (sorry Tun!).

 

[TWI]

Stock Baron, I do not have such DDE links to cope with so no I didn't, I just thought it was worth a try as it works when simply combining cell contents.
Take it by your reply that this was not successful.

 

[Tun]

Re: IQLink Macro

Always beware macro's which are recorded - they ALWAYS break! Never reference cells as Tun has done (sorry Tun!).

No need to apologise. In fact cheers for the info

My VBA knowledge is amateur to say the least. My VBA background consists a skim read of an "In a Nutshell book" and a fair bit of googling everytime I need to write a script, so I'm prone to "bad practice".

It wasn't recorded, but I can see how you came to that conclusion from the comments. The comments are a hangover from the way I start the script.

You've also done the .Formula bit which I didn't know about or have the time to look up. So I've learnt a bit from reading your script

Cheers

 

[youwho]

If you are doing this as a means of keeping the information on the screen while you scroll left- to right etc, you might want to try freezing the panes. This is achieved by going to Window on the main menu then Freeze Panes (you should select the cell that is directly to the right of you column of names and directly below the row of headers, should be the top left cell containing the retreived data).

 

[RedE]

Excel knowledge

For anyone who is interested, the best resource, by far, for learning how to program Excel, is "Excel 2000 VBA Programmers Reference" published by Wrox Press. It is now on the 2002 version.

Serious traders can only benefit from knowing how to drive Excel to its limits - and beyond.

Tun, have a look at it!

Cheers

 

[Tun]

Cheers, if I need to get into the VBA more I'll have a look.

Just out of interest (I know I should google for this but I'm here now ) where do people get their raw data from to put into Excel ?

I know there's yahoo finance, and possibly something on bigcharts, I'm just looking for other ideas. Another reason is if I start spread betting I'd like to check the minute by minute price as I've read some companies ignore stops or activate them even if the price didn't actually go there. I'm not averse to paying an appropriate fee if the service is good.

Thanks

 

[Finirama]

Need to some if anyone can offer?? I'm bit of an excel novice and a bit emarrassed to ask. I have all the values i need in cells

Wot i need a formula which states that

if close > open and price > A-ema > B-ema then = price or 0
if close < opem and price< A-ema < B-ema then = price or 0

Whenever i try i come up with syntax error.

Thanks in Advance
Fin

 

[ChowClown]

..not sure I understand what you're after but:

=IF((AND(close>open,price>A-ema))>B-ema,price,0)

this translates to:

IF (close>open AND price>A-ema) is > B-ema, THEN price, ELSE 0

 

[youwho]

try this...


=if(and(close>open,and(price>A-ema,price>A-ema)),price,if(and(close<open,and(price<A-ema,price<A-ema)),price),0)

 

[youwho]

formula was cut off at the end for some reason so will try posting it again...


=if(and(close>open, and(price>A-ema, price>A-ema)), price, if(and(close<open, and(price<A-ema, price<A-ema)), price), 0)

second time lucky!

 

[Finirama]

Sorry, should have added...the formulas i have been using are-

=IF((close>open),(price>A-ema),(price>B-ema), price,0)

and vice versa

 

[Finirama]

ChowClown and YouWho..thank you very much for the quick response

 

[youwho]

third times a charm....

=if(and(close>open, and(price>A-ema, price>A-ema)),
price, if(and(close<open, and(price<A-ema, price<A-ema)),
price), 0)

 

[Salty Gibbon]

Some ( but not all ) of my excel spreadsheets have suddenly stopped allowing me to copy formulas down / across or wherever.

Does this mean that for these specific spreadsheets I have somehow changed some settings ?

 

[youwho]

what message do you get?

 

[Salty Gibbon]

No message at all. I copy the formula down, eg =(b12+b13) becomes =(b13+b14) but the answer is the same as for b12+b13.

Weird !