Hi,
I have a market data linked to excel through DDE link so prices are updated real time.
Cell A5 contains last traded volume which updates whenever a trade is done.
I would like to SUM/ADD all last trade volume in cell C5. Is there any function or macro can help me to do this?
Thx in advance.
Create a dynamic named range for the cells you want to sum then use this named range as the range reference in the SUM Functions.
example: assumes values to sum are in column-A, sheet1 and cell A1 is a header
Dynamic Named Range formula: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
and the rnage is given the name "tradevol"
Sum formula: SUM(tradevol)
Dynamic named ranges automatically expand/contract with additions/deletions of data so a SUM formula based such a range will remain up to date.
There is just one cell A5 which keeps changing volume. I want to keep record of all volume and sum it up...
I could not understand your solution.
You might get away with using Iteration but it will be open to error, eg:
C5: =SUM(C5,A5)
To use the above you need to enable Iteration in Calc Options and set Max Iteration to 1... however this approach is prone to error as C5 will recalculate as and when any Volatile action is performed in the file.... (ie irrespective of A5 changing).
Re: VBA: A DDE update won't invoke the Worksheet_Change event unfortunately, only the Calculate event and this would be (in essence) Volatile also
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I was chatting to Colin Legg about this a few weeks ago, ie how to elegantly capture the update of a DDE link and this is what he said to me:
I think the idea of the Active X TB is particularly clever...Workbook.SetLinkOnData() method, but IIRC calcs need to be set to automatic(?).
I used to use Reuters at another job and I used to refer to Willow Solutions' site because there's lots of good stuff on there. For example, here's a question re. Reuters and SetLinkOnData:
http://www.willowsolutions.com/talk/view_msg.asp?id=61
Another workaround is to use an ActiveX textbox which is linked to the cell. When the cell changes, the textbox's Change event is raised.
Sorry - made a wrong assumption other cells also contained values.There is just one cell A5 which keeps changing volume. I want to keep record of all volume and sum it up...
I could not understand your solution.
If you can use VBA, you could write the previous of A5 into another cell when the value in A5 changes.
I'm not sure, though, if a data stream via DDE will fire the code to copy the value. If it doesn't work in the Worksheet_Change event, it might work in the Worksheet_Calculate event if there are other calculations going on in sheet.
Adjust cell B5 to the cell of choice.
C5 formula: SUM(A5:B5)
Please Login or Register to view this content.
EDIT: can probably disregard this. I just saw DO's post.
Let me try this approach. Markets going to open now. I will keep you updated. Thanks again for the replyOriginally Posted by Palmetto
Sorry - made a wrong assumption other cells also contained values.
If you can use VBA, you could write the previous of A5 into another cell when the value in A5 changes.
I'm not sure, though, if a data stream via DDE will fire the code to copy the value. If it doesn't work in the Worksheet_Change event, it might work in the Worksheet_Calculate event if there are other calculations going on in sheet.
Adjust cell B5 to the cell of choice.
C5 formula: SUM(A5:B5)Please Login or Register to view this content.
EDIT: can probably disregard this. I just saw DO's post.
Calculate event def works to trigger and copy prices from DDE link. Let me play around and get back to you. Thanks for the update.
The only problem using the Calc event is that you don't know categorically whether it was the DDE update that triggered the sheet recalculation... and given you may in theory have an update where price/quantity does not alter between updates you can't necessarily assume that a change is flagged by prior <> current ... make sense ? So in my eyes at least this is susceptible to error.... but I'm an overly cautious type by nature
Sorry tried using macro and suim function but it does not work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks