Excel according pc time

Take a look at the attachment.

Artik

Hi @Artik,

In this file Cells are being dragging almost after every One Second.

But i want that cells should be dragged after One minute according to my PC time.

Please change the code so that cell are dragged after every one minute.

Please Login or Register  to view this content.

Hi @Logit

This code is not working.

It runs very well here. I don't know what to advise.

Hi, @Logit

I did not know anything about VBA Coding. Yesterday i tried your code, but it was not working. Now i tried again and hopefully it worked.


But there is one new issue. Please solve this


Let's say time right now on my PC is 9:00:10 AM (hh:mm:ss)

Now if i open the excel file and click on "Start Button" when the time on my PC is 9:00:10 AM (hh:mm:ss), code drags cells down exactly after one minute at this time 9:01:10 AM (hh:mm:ss). I did not want that.
i want that even if click on "Start Button" at 9:00:10 AM (hh:mm:ss). the code should drag one cell down at exactly 9:01:00 AM (hh:mm:ss). and so on.

(File is attached)

Hi, @Artik


There 2 issues in this file

No#1
In this file cells are being dragging after every passing second.

No#2
Let's say time right now on my PC is 9:00:10 AM (hh:mm:ss)

Now if i open the excel file and click on "Start Button" when the time on my PC is 9:00:10 AM (hh:mm:ss), code should not drag cells down exactly after one minute at this time 9:01:10 AM (hh:mm:ss). I did not want that.
i want that even if click on "Start Button" at 9:00:10 AM (hh:mm:ss). the code should drag one cell down at exactly 9:01:00 AM (hh:mm:ss). and so on.


In a last thread https://www.excelforum.com/excel-pro...ne-minute.html

you gave me a file, which works very well according to my need. it drags one cell down after exactly one minute according to my PC time.

But now i need two more modifications
More cells dragging
Previous formulae output values should be written as simple numbers (as explained in this post)

Can you please modify these two requirements in this old file / or make changes in this new file according to my needs.

(old file is attached)

Hi, @Artik

Please reply to me

Replace the code in Module1 with the following:

Please Login or Register  to view this content.

Artik

Hi, @Artik

This code is working well. But there is a new issue coming please take a look at this.

In cells C1 and D1 i have tick data which is updating live. This tick data is being saved from cells C2 and D2 onward. Now issue is that whenever one minute is about to complete i.e 58 or 59th of second in minute, multiple same data is produced. I have highlighted these cells in Yellow color. C6:D6 to C12:D12, C58:D58 to C63:D63 and C104:D104 to C108:D108 are Highlighted in yellow are examples of it. This data was produced on its own when a minute was about to over.

Without using this code, no such same data is produced. so, issue is related to this code. Please take a look at this issue and try to solve it.

(file is attached for further help)

Originally Posted by wb35102

In cells C1 and D1 i have tick data which is updating live. This tick data is being saved from cells C2 and D2 onward.

It's a shame you're only mentioning this now and not at the start of the thread. Looks like my work so far will go in the bin. A completely different approach to the problem is needed.

Originally Posted by wb35102

Now issue is that whenever one minute is about to complete i.e 58 or 59th of second in minute, multiple same data is produced. I have highlighted these cells in Yellow color. C6:D6 to C12:D12, C58:D58 to C63:D63 and C104:D104 to C108:D108 are Highlighted in yellow are examples of it. This data was produced on its own when a minute was about to over.

From the data presented in the sheet, it appears that your computer's clock is 2-3 seconds faster than the DDE server's clock. Then by which clock should the data be collected? If relative to your computer, it will not be data from exactly full minute. It will be as it is now. With respect to the DDE server, we are not sure that data with a time stamp of a full minute will appear (the server generates data only when there are changes, not in equal time intervals). In this case we can take the data from the full minute (if there is any) or the first data after the full minute.

Originally Posted by wb35102

Without using this code, no such same data is produced. so, issue is related to this code

Let's clarify. It is your code (in Worksheet_Calculate procedure) that is causing the problem, not mine. If I had known about it earlier I would have done something about it. Using the Worksheet_Calculate event is not an appropriate method of monitoring changes coming from DDE links, although it is probably the most commonly suggested method.
Tell me what else I don't know about your project? E.g. where do the data in columns B and E:I come from? Do you actually need to use formulas in columns J:AM? Maybe let the macro calculate those values?

I have looked through your earlier threads and comments have come to mind.
Helpers on the forums greatly appreciate it when the questioner ends the thread with an opinion on the suggestions made to solve the problem. It is very important for us, because thanks to the feedback we can analyse what solution can be the best for a given problem. Please remember that not only those asking questions but also those answering them want to learn from the forum.
As penance for your sins , go through your threads, give feedback, and if your problem has been solved, then mark the thread as SOLVED (Edit First post>Advanced>Change Prefix). And then I think about how to solve your current problem (I actually already have a solution in my head).

Artik

Hi @Artik


I did not know anything about VBA coding. I did not know that some previously added code may interact with the new code. I thought that i would make it complex if i link it with the other tick data saving code. In my view, this tick data saving code would not have any affect on new code. So, i try to keep it simple. But, from the next time, I will remember this and explain everything in the starting of thread.

As you said in my worksheet Worksheet_Calculate procedure is causing issue. Few weeks ago, i want to save live tick data in excel. I searched on Google about this that time and on a site i found this code in a reply of someone. Then i tried this code and it worked. But i really did not know either this is right code for this task or not. If you have any other approach for saving tick data and this code also would not have any affect on cell dragging code then please do that.

where do the data in columns B and E:I come from?
That data is linked with C1 onward values. (See file attached)

Do you actually need to use formulas in columns J:AM?
Yes i want to use Formulas in Column J:AM.

Also i have marked the threads as solved which has solved my problem (I will remember this also and mark my thread as solved as soon as it has solved my problem)

1. Which clock do you want to use? From the DDE server or your computer. Note the disadvantages of either solution.
2. Do you insert formulas in columns B and E:I manually? If manually, at what point? Because there is nothing in the code about this.
3. Do you need to see the data in columns J:AM in real time, or are you interested in these results after the timer has stopped only?
It is important that the macro that will run at a certain time performs as few actions as possible. This has to do with the loss of data. If a macro is running at a given time and the server has just supplied new data, this data will be lost. The DDE link is not buffered, so the data is lost.

Artik

Hi @Artik

Here are the answers of the questions

1. Which clock do you want to use? From the DDE server or your computer. Note the disadvantages of either solution.
I am not sure about this which clock to choose. Is there DDE Server / Computer clock have any affect on data loss or on coding? If both clocks have not any such affect then i want to use my computer clock.

2. Do you insert formulas in columns B and E:I manually? If manually, at what point? Because there is nothing in the code about this.
Formulas in Columns B4 and E4:I4 will be inserted by me once and then i will just drag them down up to B30000 and E30000:I30000. These are not linked to Code.


3. Do you need to see the data in columns J:AM in real time, or are you interested in these results after the timer has stopped only?
I want that no data should loss at all. Please choose any of these two options that does not affect the data loss.

I am afraid that some of the formulas are incorrect. In columns M,O,T,U,V the dates are added to the values calculated from the volume rate or multiplied. However, in column Z, a fixed value of 25 is added to the date. If column D is, for example, 23/05/2022 15:33:58 (UK format) then in column Z we get 17/06/2022 15:33:58. I don't think that's what you meant. Can you provide the correct formulas for the columns indicated?

Since you don't care much about the results on the fly, they will only appear when the timer is turned off.

Artik

Hi @Artik

File with updated formulas is attached.

I do not know whether the concept of the programme has changed for you or whether I have misunderstood something.
To post #10 we were talking about calculating every 1 minute. The results are to be placed in the J:AM column range.
Since post #11 the situation has changed. You collect in columns C:D the data from the DDE server on the fly. The formulas in columns B and E:I calculate something there.
Now the question. Are the calculations in columns J:AM to be run for each row of data from columns B:I, or are we still talking about calculating every 1 minute (taking "snapshots")? That is, a full minute has passed, the UpdateData macro is run, which retrieves data from the last row of data in columns B:I (e.g. row #60). Then, calculations are performed according to the formulas in columns J:AM, but the result is written to the first free row in column J (e.g. row #5). So, in columns B:I you have data collected on the fly, and in columns J:AM you have "snapshot" results from full minutes. Is this how it should work?
However, are the calculations for all the rows of data from columns B:I?

Artik