Using In Excel

[ub27686]

I recently downloaded QLink. It works nice in downloading quotes in Excel, like =QLink|Bars!'6A M9,D,1,C'. But I would like to be able to reference a cell with the contract code (6A M9) in it for portfolio tracking and risk control. I want to use it like a formula in referencing cells.

Any suggestions?

Are there any other resouces for QLink such as sample files?

-Thanks

[ub27686]

Let me make my question a bit more clear. Currently, to get a quote, I need to enter something like this: =QLink|Bars!'6A M9,D,1,C'. What I would like to be able to do is to use QLink like all of the other Excel functions where you can reference cells. So for example I have a spreadsheet that has "6A M9" is cell "C2". I would like to have a function that would allow me to enter GetQLinkD(C2) for a daily quote of the last price. I attempted the function below but could not get it to work.


Public Function GetQLinkD(sSymbol As String) As Double

ChannelNumber = Application.DDEInitiate("QLink", "Bars")
rValue = Application.DDERequest(ChannelNumber, sSymbol & ",D,1,C")
Application.DDETerminate ChannelNumber
GetQLinkD = rValue

End Function

Any sugestions on what I am trying to accomplish or what would make the code work?

Thanks

[ScottJ]

Have you tried the snapshot formula vs the bars one? Scroll down on this article for the syntax.

Another alternative would be to add a simple DDE Links and try that. I haven't tried running QLink and DDE links together but I can't think off-hand why there would be an issue.

Thanks.

[tradertom]

Hey Mark,

Dunno how much you know about VBA but I got around this by writing a vba macro attached to a command button that basically inserts a Formula Array of my choice. It does a couple of things. Clicking the button first clears any existing formula array, then inserts the new formula array containing the symbol in B2. It is not that hard and you can customize it a lot more (I have a check box for all sessions and also cells defined for row count, etc that modify the formula array string in the vba proc). See below:

With Worksheets([your ws name here in quotes])
.Range(Your Output Range all the way down to row 65536).ClearContents
.Range(Your Output Range).FormulaArray = "=QLink|Bars!'" & .Range("B2").Value & "," & .Range("B3").Value & "," & .Range("B4").Value & ",DTOHLCV,FILL,REVERSE" & strMarketHours & "'"
End With

(If you are writing this code in the worksheet itself, you can use "With Me"..."End With" instead of the worksheets(worksheetname) identifier.) The code above assumes Your Output Range is the address range of the cells where your output is going to be, B2 is your symbol, B3 is your interval (in minutes; leave off strMarketHours if you want DWMQ or Yearly data), B4 is the number of rows you want to display, and strMarketHours is a string formatted like "09:30-16:00" or whatever market hours you want. An alternative to using a command button would be to code this stuff in Worksheet_Change to do the work for you when values in B2, B3, or B4 change using the Intersect function.

You can do the same sort of coding for use with snapshots and ticks as well, even on the same worksheet, just modify the syntax of the FormulaArray string. Oh, and it is much easier to work with if you use range names instead of cell addresses (e.g. .Range("StockSymbol").Value). Play around with it and have fun!

All the best,
Tom

[ub27686]

Thanks for the replys.

Scott, I found the data that I need in the Bars request. The standard DDE in eSignal is the same type of call as QLink according to the documentation. Neither are Excel function friendly.

Tom, I got your code working nicely. Thanks for the code. I will play around with it and see if I can get it encapsulated into an Excel function. I will follow up with a post later.

Thanks