Via a dde link, I have a constantly changing (real-time) value in cell
A1.

How can I automatically copy and add the latest value every 1 minute
into a column?

Any suggestions much appreciated.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

grant606 wrote:
Via a dde link, I have a constantly changing (real-time) value in cell
A1.

How can I automatically copy and add the latest value every 1 minute
into a column?

Any suggestions much appreciated.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Hi Grant,

Out of curiosity what real time application/feed are you using?
(Reuters or Bloomberg or something else?)

Regards,
Bondi

Dear Bondi,

Reuters or Bloomberg? I wish.

No, I'm using WinBis from a German company, and possibly the cheapest
at £68 per month for real-time Eurex futures and options. If there is a
cheaper alternative, I'd like to know.

My specific current interest is refining an Excel programme I've built
for volatility arbitrage of index options (DAX, STOXX, SMI).

Grant.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Hi Grant,

So if i understand you, then your goal is to creat an intraday history
with one minute updates on the above listed instruments.

I know for a fact that this is hell to creat in Excel. Even if you have
a live feed. I'm not sure about how it works when reciving from the web
but i cannot belive it will be better.

It also depends on how long series you want to store/analyse.

I'm sorry not to be of any more help but maybe your trades are not spot
and hence you can download historical data from euronext.com or
something like that. I belive they have intraday data.

Regards and good luck,
Bondi

Paste into a standard module:

'---------------------------------------------------------------------
Option Explicit

Dim dNextExec As Date

Sub CopyValue()
Dim rCell As Range
With Worksheets("Data")
Set rCell = .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
rCell.Value = Range("A1").Value
rCell.Offset(0, 1) = Now
End With
dNextExec = Now + TimeSerial(0, 1, 0) 'h, m, s
Application.OnTime dNextExec, "CopyValue"
End Sub

Sub StopTimer()
Application.OnTime dNextExec, "CopyValue", , False
End Sub

'---------------------------------------------------------------------

Cheers,
--
AP
"grant606" a écrit
dans le message de news:
...

Via a dde link, I have a constantly changing (real-time) value in cell
A1.

How can I automatically copy and add the latest value every 1 minute
into a column?

Any suggestions much appreciated.


--
grant606
------------------------------------------------------------------------
grant606's Profile:
http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Dear Ardus,

VBA is totally alien to me so I’ll explain what I did to show if I got
it right (or not)

Opened new file in Excel and named it New Timer.xls.

Opened Visual basic Editor, Insert, Module.

Module headings are left box (General), right box Copy Value.

I then copied/pasted the code.

Option Explicit
Dim dNextExec As Date

A line underlines these two.

(The rest of the codes follows.)

The last three lines:

Sub StopTimer()
Application.OnTime dNextExec, "CopyValue", , False
End Sub

have a line above them.

I will need to wait until tomorrow before I can run it. It is really
decent of you to provide this, and greatly appreciated. If I can
reciprocate in any way, let me know (are you in the UK?)

Bondi, It's a nightmare to find the data I’m looking for from any
source; I’ve looked at all of them. Eurex provides tic data but the
files (for a single day) are enormous and contain 90% of data I don’t
need. To extract the relevant data is an exercise in itself.

Best regards,

Grant.



--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

You've done all right.
Now, you must reestablish your DDE link to get real-time data in Worksheet
"Data" cell A1.
Once this is done, you can run CopyValue macro.
To stop it, run StopTimer macro.

HTH
--
AP

"grant606" a écrit
dans le message de news:
...

Dear Ardus,

VBA is totally alien to me so I'll explain what I did to show if I got
it right (or not)

Opened new file in Excel and named it New Timer.xls.

Opened Visual basic Editor, Insert, Module.

Module headings are left box (General), right box Copy Value.

I then copied/pasted the code.

Option Explicit
Dim dNextExec As Date

A line underlines these two.

(The rest of the codes follows.)

The last three lines:

Sub StopTimer()
Application.OnTime dNextExec, "CopyValue", , False
End Sub

have a line above them.

I will need to wait until tomorrow before I can run it. It is really
decent of you to provide this, and greatly appreciated. If I can
reciprocate in any way, let me know (are you in the UK?)

Bondi, It's a nightmare to find the data I'm looking for from any
source; I've looked at all of them. Eurex provides tic data but the
files (for a single day) are enormous and contain 90% of data I don't
need. To extract the relevant data is an exercise in itself.

Best regards,

Grant.



--
grant606
------------------------------------------------------------------------
grant606's Profile:
http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Ardus,

You're a Gentleman, Scholar and Saint.

Thank you, mate.

Grant.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Dear Ardus,

Started programme as instructed; following messages appeared:

On Run CopyValue, “Runtime Error ‘9’ Subscript out of range”

On Run StopTimer, “Runtime Error ‘104’ Method ‘OnTime’ of
object_Application failed"

Any suggestions, please?

Regards,

Grant.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

I am also working with a DDE and have questions on updating my data
tables. I hope you both don't mind my participation in this thread.

My Dilemma: IF PRICE HIGH, THEN capture the new HIGH value, ELSE
show current HIGH value.

Solution 1: Uncertain how to approach this problem, I attempted to go
with something familiar: =IF(PRICE HIGH, PRICE, HIGH).
Unfortunately, I discovered this does not record the new price. It
only confirms the condition that a new HIGH has been reached during the
current period.

Solution 2: Create a macro to paste PRICE values to HIGH.
While this solution does record the change in values, I have not been
able to automate the macro.

Sub Macro1()
'COPY PRICE
Range("PRICE").Select
Selection.Copy

'PASTE VALUES TO HIGH
Range("HIGH").Select
*Selection.PasteSpecial Paste:=xlValues*, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

My questions are,
1) Is it possible to create a function using the
*Selection.PasteSpecial Paste:=xlValues * vba command?

2) Can vba and macro commands be written into a conditional format or
into a Logical Function formula? Into other types of Excel Functions?

3) In your opinion, what is the most effective method for automating
the paste values vba command?

4) What skill sets should I review before creating this next solution?
By the way, I have access to Jeff Webb's "Using Excel Visual Basic for
Applications," "Excel Bible 2003," and "MOS 2003 Study Guide," not to
mention access to study sections at sites like ExcelTip, so review and
learning shouldn't be much of a problem.

Thanks.


--
FX42N
------------------------------------------------------------------------
FX42N's Profile: http://www.excelforum.com/member.php...o&userid=34923
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Change line:
With Worksheets("Data")
and replace "Data" with your worksheet name.

HTH
--
AP

"grant606" a écrit
dans le message de news:
...

Dear Ardus,

Started programme as instructed; following messages appeared:

On Run CopyValue, "Runtime Error '9' Subscript out of range"

On Run StopTimer, "Runtime Error '104' Method 'OnTime' of
object_Application failed"

Any suggestions, please?

Regards,

Grant.


--
grant606
------------------------------------------------------------------------
grant606's Profile:
http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Ardus,

No joy, I'm afraid. Searching for Error Messages via Google, a thousand
and one possibilities are presented. However, unbeknown to myself, there
seems to be a difference between workbooks, worksheets and files.

One explanation said there had to be a minimum of three sheets to a
workbook. All I know is I have a file called MY TIMER.xls - there are
no sheets (I set the default to 1). Now I have changed this to 3. In
vain.

Should the code still refer to
With Worksheets ("MY TIMER")
(and should I include the .xls suffix)?

Grant.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264

Hi Grant

i would to do exactly the same thing with DDE coming from metatrader
and i know nothing about VB did you achieve your goal ,it' is possible
to have a sample of your script to build a database coming from DDe
link .
i'm trading full time on forex i'm using tick data to get signal ,and
today i have to stay all day in front of my computer to key in data to
build my database of ticks

your help will be much appreciated

thank's in advance

Philip

grant606 wrote:
Via a dde link, I have a constantly changing (real-time) value in cell
A1.

How can I automatically copy and add the latest value every 1 minute
into a column?

Any suggestions much appreciated.


--
grant606
------------------------------------------------------------------------
grant606's Profile: http://www.excelforum.com/member.php...o&userid=35077
View this thread: http://www.excelforum.com/showthread...hreadid=548264