-2

I am currently trying to handle a data connection in Excel and I would like to populate some cells dynamically as the data is periodically changing.

More precisely, I have an Excel worksheet which is updated every 3 minutes through an internet data connection -stock market updates- during a time window of, say, 8 hours. Every 3 minutes the content of a cell (a decimal number), say, B1 is updated.

I would like to know if it is possible to "record" every number B1 throughout the 8 hours in a column on my worksheet. 8*60/3=160, hence I would like to dynamically populate 160 cells with the content of cell B1.

If it is possible, I then would like to take the maximum out of these 160 cells (using the MAX(,) function), record that number in another column and plot the graph of the evolution of the value of B1 over the 8 hours time window. The idea is to do this on daily basis so that after a week or month I could be able to look at the worksheet and assess the market trends.

Following the answer and comments, I have tried to write some VBA subroutines but I am struggling to get them to actually work. Here is my code:

Dim RunTime As Date
Dim j As Integer

Sub CopyCell()
Sheets("test").Cells(j, 3) = Sheets("test").Range("B1").Value
j = j + 1
'If Time >= TimeSerial(20, 0, 0) Then'
    Application.OnTime RunTime, "CopyCell()", , False
End If
End Sub

Sub Main()
MsgBox "Starting Macro"
j = 0
RunTime = Now + TimeValue("00:03:00")
Application.OnTime RunTime, "CopyCell()"
End Sub

For the meantime I trigger the Main() sub myself and expect to make it stop at 20:0:0 (8pm). When I trigger the Main() sub I get the message "CopyCell() is not available in this workbook", but I do have copied the above code in the ThisWorkbook tab in the VBA editor and I have enabled all macros. Note that if I run the CopyCell() sub alone, the content of cell B1 is copied to cell C1.

Can you help me figure out what is wrong in my code?

Thanks, -David

pnuts
  • 58,317
  • 11
  • 87
  • 139
david
  • 127
  • 3
  • 9
  • Hi, I really don't have any idea how to do this... it is the first time I use Excel with data connection and I only know how to read the updated data. I am guessing that there must be a function to copy the data from a cell on periodic basis but I cannot find it. – david Apr 11 '13 at 02:28
  • There is not a built-in formula or function to do what you are asking precisely, you can probably find a mix and match to do it but the easiest thing to do would be write it to some sort of database and analyze it there, as you will be writing thousands of records per week at least – PW Kad Apr 11 '13 at 03:32

1 Answers1

0

Without your code it is difficult to give you code on how to do this exactly, but in basic terms you can do this pretty easily. Whenever your refresh is fired simply have VBA write the data to a separate worksheet or a database (preferably a database like Access because it works happily with Excel and you don't need to save the Excel workbook)

PW Kad
  • 14,953
  • 7
  • 49
  • 82
  • Hi Kadumel, I currently don't have any code written. I simply have a sheet named "data" where the stock market data is updated and a sheet named "analysis" where I want to do my analysis. Currently I have cells in the "analysis" which are equal to "=data!Bx" where "x" is a row number and I simply use this information to do my analysis. I never coded a VBA script in Excel but I will give it a try. Thanks – david Apr 11 '13 at 05:23
  • Hi I have updated my question with some VBA code according to your comments. – david Apr 17 '13 at 03:40
  • I have revised my question so that it now includes the original question which I had mistakenly removed after my first revision – david Apr 18 '13 at 00:40