Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post

    Download Json Data to excel

    I want to download the below json data to excel using vba macro and parse the information

    http://finance.google.com/finance/in...ACC,NSE:AIAENG

    http://finance.google.com/finance/in...NSE:JETAIRWAYS

    http://finance.google.com/finance/in...AUKRI,NSE:INFY


    and want to pull the above data every 30 seconds.

    thanks in advance,
    Bhushan

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    Thanks for your reply.. But i intend to work on Json Data...

    which is different from the examples given in the links.

    Regards
    bhushan






  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Json is just a data format so once you have the data it's just a matter of making it look nice.
    Why don't you get the data downloaded and showing in the spreadsheet, then post your result and we can suggest ways to format it nicely - or visit this thread for some ideas.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Bhush,

    The following code will extract your data from all 3 web sites every 30 sec and format it into columns. You did not specify how you wanted it formatted so I took a guess (see image). Click the button to start the code.

    HTH,
    Maud

    Bhush1.png

    Code:
    Sub WebData()
    '---------------------------
    'DECLARE AND SET VARIABLES
    Dim v
    v = Array("http://finance.google.com/finance/info?client=ig&q=NSE:ABB,NSE:ACC,NSE:AIAENG", _
                "http://finance.google.com/finance/info?client=ig&q=NSE:JSWENERGY,NSE:JSWSTEEL,NSE:JETAIRWAYS", _
                "http://finance.google.com/finance/info?client=ig&q=NSE:INDUSINDBK,NSE:NAUKRI,NSE:INFY")
    '---------------------------
    'GET WEB DATA
    Cells.ClearContents
    Application.ScreenUpdating = False
    For I = 0 To 2
        With ActiveSheet.QueryTables.Add(Connection:="URL;" & v(I), Destination:=Cells(2, (I * 2) + 1))
            '.Name = "URL " & I + 1
            Cells(1, (I * 2) + 1) = "URL " & I + 1
            .Refresh BackgroundQuery:=False
        End With
    '---------------------------
    'FORMAT DATA
        FormatData (I * 2) + 1
    Next I
    ActiveWorkbook.Connections("Connection").Delete
    ActiveWorkbook.Connections("Connection1").Delete
    ActiveWorkbook.Connections("Connection2").Delete
    For Each nme In ThisWorkbook.Names
        nme.Delete
    Next nme
    Application.OnTime Now + TimeValue("00:00:30"), "WebData"
    Application.ScreenUpdating = True
    End Sub
    
    
    Public Sub FormatData(col)
    '---------------------------
    'FORMAT DATA
    LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
    For I = LastRow To 1 Step -1
    '---------------------------
    'REMOVE UNWANTED CHARACTERS
        Cells(I, col) = Replace(Cells(I, col), "{", "", 1, , vbTextCompare)
        Cells(I, col) = Replace(Cells(I, col), "}", "", 1, , vbTextCompare)
        Cells(I, col) = Replace(Cells(I, col), "/", "", 1, , vbTextCompare)
        Cells(I, col) = Replace(Cells(I, col), "[", "", 1, , vbTextCompare)
        Cells(I, col) = Replace(Cells(I, col), "]", "", 1, , vbTextCompare)
        If Left(Cells(I, col), 1) = "," Then
            Cells(I, col) = Right(Cells(I, col), Len(Cells(I, col)) - 1)
        End If
    '---------------------------
    'DELETE BLANK ROWS
        If Cells(I, col) = "" Or Cells(I, col) = " " Then
            Cells(I, col).EntireRow.Delete
    '---------------------------
    'SPLIT DATA INTO COLUMNS
        Else:
            On Error Resume Next
            Cells(I, col + 1) = Split(Cells(I, col), " : ")(1)
            Cells(I, col) = Split(Cells(I, col), " : ")(0)
            Cells(I, col) = Replace(Cells(I, col), ":", "$", 1, 1, vbTextCompare)
            Cells(I, col + 1) = Split(Cells(I, col), "$")(1)
            Cells(I, col) = Split(Cells(I, col), "$")(0)
            On Error GoTo 0
        End If
    Next I
    End Sub
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    bhushanvshah (2016-11-03)

  7. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Maud to the rescue, as usual.

    cheers, Paul

  8. The Following User Says Thank You to Paul T For This Useful Post:

    Maudibe (2016-11-03)

  9. #7
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    Thank you Maud, It works beautiful... and the format is excellent ... cheers and thanks once again..
    bhushan

  10. The Following User Says Thank You to bhushanvshah For This Useful Post:

    Maudibe (2016-11-03)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •