Replay data & Excel

[FTSE Beater]

Hi All

I'm trying to do something a little unusually.
Is there a way of getting the Bar replay or Tick replay data into Excel? Ideally the last bars OHLC data.

I'm doing a presentation, and I need one laptop to run E-Signal in Bar replay mode, while the other laptop recieve the OHLC data of the bar that has just been shown. Is there a way of doing this, or have I let my imagination run away with me

[eSignal_JasonK]

FTSE Beater,

I'm not sure if you can use this to connect the data between 2 laptops. I would recommend hooking up an external monitor to one of your laptops and extending your desktop for dual monitor display. Have Excel on one and eSignal on the other. The details and an example formula for the DDEOutput() can be found in Chris Kryza's EFS Help File, which can be found in the Specialty Scripts group in file share.

Your formula will look something like this:

DDEOutputOHLC.efs

var ddeOpen = new DDEOutput("pbOpen");
var ddeHigh = new DDEOutput("pbHigh");
var ddeLow = new DDEOutput("pbLow");
var ddeClose = new DDEOutput("pbClose");

function preMain() {
    setPriceStudy(true);
    setStudyTitle("DDE OHLC");
    setShowCursorLabel(false);
}

function main() {
    var vO = open();
    var vH = high();
    var vL = low();
    var vC = close();
    
    if(vO != null) ddeOpen.set(vO);
    if(vH != null) ddeHigh.set(vH);
    if(vL != null) ddeLow.set(vL);
    if(vC != null) ddeClose.set(vC);
    
    return;
} 

The link below is an example Excel spreadsheet with the DDE links you would need.
DDE_OHLC.xls
Basically, the DDE formulas you would enter in Excel will look like this:

=eSignal|EFS!pbOpen
=eSignal|EFS!pbHigh
=eSignal|EFS!pbLow
=eSignal|EFS!pbClose

You just have to make sure the formula is running in eSignal, otherwise the DDE links will not receive any data. I tested this in bar replay mode and it works fine. I think this should also work in playback mode. Hope this helps.

[FTSE Beater]

Hi Jason

THAT IS SIMPLY SUPERB!!!!
You've saved me so much work, and the presentation looks a lot lot more professional

Thanks again

[whammer]

JasonK,

I'm new to EFS and EXCEL DDE.

I manage to put the EFS you provided into the eSignal editor. How do I run it?

And how do I specify what stock symbol on the EXCEL spreadsheet using "=eSignal|EFS!pbOpen" ?

I'm also interested in getting the latest (real-time) tick price of a symbol, because I keep track of 20 or so stocks in my spreadsheet and right now I manually enter the prices to see what P/L I have at the current tick price. It's a pain in the xxx.

Thank you in advance for any help.

WW

[eSignal_JasonK]

Hello WW,

Visit our DDE FAQs to learn more about DDE.

After saving a formula, or .efs file, to \eSignal\Formulas\, right-click on an advanced chart and select the formula from the "Formulas" menu to apply it to your chart.

The formula in this thread was not set up to be symbol specific. I've modified the formula to incorporate the chart's symbol. FYI, for symbols like, ES #F, only "ES" will be used for the symbol in the DDE link. For symbols such as, ES M4, the spaces will be replaced with underscores (i.e. ES_M4) Download the updated version below and use the following format for DDE links in Excel.

DDEOutputOHLC.efs

ES #F
Example DDE Link: =eSignal|EFS!ES_Close

IBM
Example DDE Link: =eSignal|EFS!IBM_Close

Alternatively, you could pull the data you need into excel directly from the Data Manager. Attached is an example spreadsheet that will show you all the qualifiers etc. For the last price on IBM, enter =WINROS|LAST!IBM .

[whammer]

Excellent, JK.

Thank you very much for your help.

W

[whammer]

BTW, do you know how to concatenate two strings in EXCEL?

I want to concatenate for example "IBM" in one cell and "=WINROS|LAST!" in another cell to form "=WINROS|LAST!IBM".

TYIA,

WW

[mooseman]

Excel Concatenation

Assuming you have something in R1C1 and something else in R1C2 and you want to concatenate the word money between the contents of the cells and store it in R1C3.
In cell R1C3 you put the formula
=R1C1&" money "&R1C2

I prefer the R1C1 reference style, located in Tools > Options > General
Otherwise it would be =A1&" money "B1

[whammer]

Thanks, mooseman!

Now, I don't have to type in the price manually on 20 stocks all the time.

W

[whammer]

When I tried =WINROS|LAST!"&B2, where B2 cell contains the symbol IBM, I get the exactly the string "WINROS|LAST!IBM".

But it doesn't execute into a number, instead it becomes just a string. Any idea?

W

[mooseman]

Excel Formul

whammer,

I am knowledgable of EXCEL but not relative to ESignal so I need to make some assumptions on your question.
your formula =WINROS|LAST!&"B2"
I am used to using =Sheet1!RC[-1] to refer to the same workbook, Sheet1 (the tab name) and get the value of the same row and 1 column left of the current cell of the sheet I past that formula in.
Your WINROS|LAST! looks like a typical reference to another sheet. The & is a text concatentation of the text in B2. Unless the name IBM has set to mean something to Excel I think it will not return the value represented by "IBM". You can name a cell "IBM" then reference that variable in a forumla and it would return the contents of the cell named IBM and not the text string IBM. To name a cell, be on the cell to name, Insert>Name>Define. Assuming you have a cell defined name to be "IBM". To use that reference in a formula on the same worksheet as the defined name, enter into any cell =IBM. That will show the contents of what is in the cell named IBM.
If the forumla referring to IBM is on a different worksheet in the same workbook use this forumla =book1!IBM where book1 is the name of the workbook. There are fancier ways to use Defined Names, read Excel help. If the names and positions of the data change, say from downloading a list repeatedly and the list changes. You could build a lookup table then use a formula to search for a value and return an associated value. That will require building a little table on a sheet where the identifier is one column like stock symbols, then following colomuns have various data like Open in next column, High in next column, etc. The lookup formula references the named table, searches for string match in specified column (like"IBM") then returns whichever column offset you want. So you could retriece the Open, High, etc.
Hope that helps. Without seeing your data source, where, how it is located I am just taking guesses.

[whammer]

Mooseman,

I have ="IBM" in cell A1 and then in cell B1, I have =WINROS|LAST!'A1'

This time I get "#Need higher symbol count in password". This is strange, and I think it has to do with the DDE. Because if I go

=WINROS|LAST!IBM

I get IBM's last price.

W

[mooseman]

whammer,

your message has =WINROS|LAST!'A1'
Try dropping the single quotes before and after A1, should be =WINROS|LAST!A1 to pick up the contents of A1

Dave

[whammer]

I did (=WINROS|LAST!A1), but EXCEL automatically adds the single quote and become =WINROS|LAST!'A1'.

??

W

[mooseman]

whammer,
Since I am not familiar with wha the WINROS is doing, where it is , etc, I may be about out of help if you call it that!

I would try doing a reference between two worksheets in the same workgroup like in my earlier posts, . Just try referencing a cell and see if it adds quotes on that. Maybe the WINROS is special somehow or maybe there is something else in EXCEL going on.