Real time STOCK Data into EXCEL

POSTED BY sunil ON August 2, 2012 4:06 pm COMMENTS (6)

Dear All,

I trade in markets using various softwares, but lately the money i make in markets are enough to feed the softwares… hence i have decided to do my own analysis, and in this endevour i request you all if you can let me know how do we bring in Live Stock prices into Excel i would be very thankful to you… this shall save me few thousand buks a quarter in softwares….

Thanks in Advance,
Sunil.

6 replies on this article “Real time STOCK Data into EXCEL”

  1. Manish awasthi says:

    Hi sunil,
    did u got any success ?
    Manish

  2. sunil says:

    Thanks guys let me work out on this weekend and will come back with results…..

  3. 3sharad says:

    There are a couple of ways to achieve this:

    1. Using Excel web connection to HTML table
    Say for example you need live prices that are depicted on a website, into excel. You can open the webpage into excel and select the relevant table and set the refresh frequency. The table would keep refreshing automatically.
    Limitation: You need a table on some website which you can link to

    2. Using VB Script: For this you need a data source that can deliver tick data as a webservice (Sunil –> Your friends are correct).
    There are various sources available to achieve this. Google and yahoo finance provide free web service (data delayed by sometime). With a little bit of coding/googling you should be done.

    Pasted at the bottom is a code that I used sometime back… it should set you rolling. You might need to put a bit more coding to bring the refreshed data into the cells and sheet that you want, may be by using VLOOKUP functions…

    Hope this helps…

    best,
    Sharad

    ——–COPY & PASTE IN A NEW MODULE IN EXCEL——————————————–

    Option Explicit
    Private Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
    Dim MainBook As Workbook
    Dim NewBook As Workbook
    Dim YahooBook As Workbook
    Dim FilePath As String
    Dim YahooPath As String

    Sub Main()
    Dim i As Integer

    Application.DisplayAlerts = False
    Set MainBook = ThisWorkbook
    YahooPath = “http://download.finance.yahoo.com/d/quotes.csv?s=HINDUNILV.NS,TCS.NS,INFY.NS,ACC.NS,ICICIBANK.NS,HDFCBANK.NS,RELIANCE.NS,%5EBSESN,%5ENSEI&f=sl1c1&e=.csv”

    Dim NoofTimes As Long
    ‘Set this to a large number
    NoofTimes = 2

    For i = 1 To NoofTimes
    ‘refreshes every 1 second
    Sleep 1000
    Call YahooData
    Next i

    MainBook.Save
    Set MainBook = Nothing

    Application.DisplayAlerts = True

    End Sub

    Sub YahooData()

    Workbooks.Open YahooPath
    Set YahooBook = ActiveWorkbook

    YahooBook.Worksheets(1).Activate
    YahooBook.Worksheets(1).Range(“A1:C2000”).Select
    Selection.Copy

    MainBook.Worksheets(1).Activate
    MainBook.Worksheets(1).Range(“A1”).PasteSpecial 12

    MainBook.Save
    YahooBook.Close False
    Set YahooBook = Nothing

    Application.CutCopyMode = False

    End Sub

    ——————————————————————-

  4. Sunil

    One simple way of doing it is to load the whole website data in excel , search how you can pull contents from a website . It can be done in excel , once you do that, the stock prices will be on a particular cell and you can tell excel to refresh in 10 sec or 1 min or so ,, then in another workbook you can reference to cells in first workbook and make some analysis .. Hope this helps . Also read this http://chandoo.org/wp/2008/06/24/get-stock-quotes-in-excel/

  5. sunil says:

    Good atleast i have a partner in my journey…
    in some other site i have asked for such infomation and i got a reply saying if we know VB Script we can do it…
    Will contact few of my frnds who knows VB Script but major part is where to pick values from so that they can be put into Excel…
    The Source itself is not visible….

    Lets hope for the best….

    Manish Sir, if you can help us out in this we shall be very thankful to you.

  6. somasekhar says:

    Dear Sunil,

    I am also looking for this type of Excel sheet. Lets hope if anyone help us.

    Thanks,
    Sekhar

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Download Our FREE Ebook!

Available only for first 100 people today

Download Our FREE Ebook!

Available only for first 100 people today