Excel/capture live data to excel
Dear Sir, Hope you doing well, I want to capture live data from website and put in a excel tables, I want to store one by one all the datas in my my excel sheets the way it changes from website the excel stores all the data without losing the previously stored datas.
The data which I want to store I have attached the jpg file for better understanding.
The website from which I want to store is "http://www.investing.com/technical/technical-studies?period=300"
Hello again Siraj,
While I didn't succeed in fixing the problem as it affects connecting to investing.com, I did get it to work with other websites. Apparently investing.com thinks I am using IE 7, a way outdated version of IE, when in actuality I'm using IE 11. I'm hoping that it is somehow different for you and you won't experience the same problem. I think it is more likely a problem with the configuration of my computer than that there is something wrong with the investing.com website, as I would imagine there are hundreds or thousands of users connecting to investing.com in this way.
So I got it working using a weather data website--I chose this because investing websites usually only update during market open hours, and I am generally working during those hours. The weather websites update 24 hours per day. The one I happened to pick updates every 15 minutes, but this is just a detail.
First I established a data connection to the tables I was interested in (see http://office.microsoft.com/en-us/excel-help/connect-external-data-to-your-workb
). This applies only to Excel 2013, but if you have 2007 or later you should be able to find similar instructions elsewhere online that apply to your version. Perhaps you have already accomplished this is not an issue for you.
Now you must paste the following code into your worksheet's code module*:
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox Target.Address & " changed."
Sub SaveData(Address As String)
Dim Today As String
Dim LastRow As Long
Dim MainSheet As Worksheet
Dim Col As Object
Today = Format(Date, "MM-DD-YY")
Set MainSheet = Worksheets(1)
If Not SheetExists(Today) Then
ActiveSheet.Name = Today
'set column widths the same as mainsheet
For Each Col In .Columns
Columns(Col.Column).ColumnWidth = Col.ColumnWidth
LastRow = .UsedRange.Rows.Count
.Cells(LastRow + 5, "A") = "Time"
.Cells(LastRow + 5, "B") = Format(Time, "HH:MM:SS")
'copy changed block to date sheet
.Cells(LastRow + 7, "A").PasteSpecial xlPasteValuesAndNumberFormats
Function SheetExists(ShName As String) As Boolean
Dim SH As Object
On Error GoTo NoSuch
Set SH = Sheets(ShName)
SheetExists = True
SheetExists = False
*To do this simply right click on your worksheet's tab, select View Code, and paste this code into the code pane.
This will automatically "fire" and create a new date worksheet (if one doesn't already exist) and write the updated data to the end of that sheet with a time stamp at the top. This code doesn't care what website or data you are connected to--it just copies whatever is there.
Do note that the code does assume that your worksheet that connects to investing.com is the first tab in your workbook. If this is not the case your will need to change the code
"Set MainSheet = Worksheets(1)" accordingly.
Feel free to follow up if you have any problems with this.
I hope you find that this does what you want.