How to Collect Real Time Data Automatically (every few seconds) and save in excel column

[KenTClark]

Hey Everyone,

First off, great discussion and help you guys provide here, cheers to all!!!

I already tried searching for something similar to what I want to do with stock index real time quotes in the forum but unfortunately couldn't find. Its a pretty simple concept I think:

Now that excel has built in real time data for stocks from their cloud business, excel can retrieve updated stock or index prices in a specified cell, every time you hit refresh.

How can I automatically ask excel to record the time and price, daily high, daily low in the rows below, at every second or minute interval ? By 4 pm I would have a dataset for the entire day collected in the same worksheet? (Please see example picture)

Right now I'm just recording the values manually whenever i have time, which is not ideal . If this can be automatically recorded in excel in the background that would be great!!! I think the data refresh rate can be changed, instead of having to press the update button, I'm also looking in to that.


I already tried copying a similar program from an (old thread~2010?) into my excel VBA and after enabling macros, excel crashes and i can't even open the test file. So that's why I'm reposting.

"Private Sub Worksheet_Calculate()
capturerow = 2

currow = Range("A65536").End(xlUp).Row

Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 3) = Cells(capturerow, 3)
End Sub"


Thank you for your assistance!!
Best Regards,
KC

 

[KenTClark]

is the problem from where I inserted the code? I just right clicked the worksheet to view code and typed the code under (General). Should i insert as procedure, userform, module or class module? would that make any difference? i'm not that proficient in VBA unfortunately.

 

[igold]

After you open the Visual Basic Editor follow these steps:

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window)

 

[KenTClark]

Yes !! that was the issue. After inserting the code in the module, it now collects automatically per the defined interval . Great help you provided igold, thanks a lot, have a great day!!

Best Regards,
KC

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!