Any EXCEL Gurus?

StockBaron

Junior member
21 0
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

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

Tun

Junior member
22 0
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

Junior member
21 0
twalker said:
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

Junior member
25 2
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!).
 

Attachments

  • iqlink macro.xls
    29 KB · Views: 422

TWI

Senior member
2,536 254
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

Junior member
22 0
Re: IQLink Macro

RedE said:
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

Junior member
27 1
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

Junior member
25 2
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

Junior member
22 0
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

Established member
552 3
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

Senior member
2,732 56
..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

Junior member
27 1
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

Junior member
27 1
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

Established member
552 3
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
 
 
AdBlock Detected

We get it, advertisements are annoying!

But it's thanks to our sponsors that access to Trade2Win remains free for all. By viewing our ads you help us pay our bills, so please support the site and disable your AdBlocker.

I've Disabled AdBlock