You are here:

Excel/capture live data to excel

Advertisement


Question
Live data
Live data  
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"

Answer
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."
  Beep
  SaveData Target.Address
End Sub

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
     Worksheets.Add after:=MainSheet
     ActiveSheet.Name = Today
     'set column widths the same as mainsheet
     With MainSheet.Range(Address)
        For Each Col In .Columns
         Columns(Col.Column).ColumnWidth = Col.ColumnWidth
        Next Col
     End With
     MainSheet.Activate
  End If
  With Worksheets(Today)
     LastRow = .UsedRange.Rows.Count
     .Cells(LastRow + 5, "A") = "Time"
     .Cells(LastRow + 5, "B") = Format(Time, "HH:MM:SS")
     'copy changed block to date sheet
     Range(Address).Copy
     .Cells(LastRow + 7, "A").PasteSpecial xlPasteValuesAndNumberFormats
  End With
End Sub

Function SheetExists(ShName As String) As Boolean
  Dim SH      As Object
  On Error GoTo NoSuch
  Set SH = Sheets(ShName)
  SheetExists = True
  Exit Function
NoSuch:
  SheetExists = False
End Function
___________________________________________________

*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.

Damon  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.