Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    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,693
    Thanks
    59
    Thanked 1,065 Times in 990 Posts

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    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,693
    Thanks
    59
    Thanked 1,065 Times in 990 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,792
    Thanks
    139
    Thanked 705 Times in 639 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,693
    Thanks
    59
    Thanked 1,065 Times in 990 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
    24
    Thanks
    8
    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)

  11. #8
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    Thanked 1 Time in 1 Post
    Dear Maud,
    Could u pls help in tweaking the code.

    1. get the website address from the cells in the sheet. (now its in the code)
    2. get the data one below the other (now its in 3 columns)

    Thanks
    Bhushan




    Quote Originally Posted by Maudibe View Post
    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

  12. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,693
    Thanks
    59
    Thanked 1,065 Times in 990 Posts
    1. Enter the URLs in your spreadsheet, maybe in A1 - A3.
    Change this:
    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:JE TAIRWAYS", _
    "http://finance.google.com/finance/info?client=ig&q=NSE:INDUSINDBK,NSE:NAUKRI,NSE:INF Y")

    To this:
    v = Array(A1, A2, A3)

    2. Do you want the data from each web site returned below the previous instead of side by side?

    cheers, Paul

  13. The Following 2 Users Say Thank You to Paul T For This Useful Post:

    bhushanvshah (2017-04-20),Maudibe (2017-05-08)

  14. #10
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    Thanked 1 Time in 1 Post
    Thanks Paul,

    Question2... i want the data in one column only. not in 3 columns.

    so it will be data from first URL.. below which will be data from second URL.. and below which will be data from third URL..

    thanks
    bhushan







    Quote Originally Posted by Paul T View Post
    1. Enter the URLs in your spreadsheet, maybe in A1 - A3.
    Change this:
    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:JE TAIRWAYS", _
    "http://finance.google.com/finance/info?client=ig&q=NSE:INDUSINDBK,NSE:NAUKRI,NSE:INF Y")

    To this:
    v = Array(A1, A2, A3)

    2. Do you want the data from each web site returned below the previous instead of side by side?

    cheers, Paul

  15. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,693
    Thanks
    59
    Thanked 1,065 Times in 990 Posts
    You need to change this line
    "With ActiveSheet.QueryTables.Add(Connection:="URLv(I), Destination:=Cells(2, (I * 2) + 1))"
    to update the row instead of column. I'm not sure I can do this - don't have Excel - but someone else may be able to help.

    cheers, Paul

  16. The Following 2 Users Say Thank You to Paul T For This Useful Post:

    bhushanvshah (2017-04-21),Maudibe (2017-05-08)

  17. #12
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    Thanked 1 Time in 1 Post
    hi maud.. could you help out on this glitch..

    thanks
    Bhushan



    Quote Originally Posted by Paul T View Post
    You need to change this line
    "With ActiveSheet.QueryTables.Add(Connection:="URLv(I), Destination:=Cells(2, (I * 2) + 1))"
    to update the row instead of column. I'm not sure I can do this - don't have Excel - but someone else may be able to help.

    cheers, Paul

  18. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,792
    Thanks
    139
    Thanked 705 Times in 639 Posts
    Paul,

    You are absolutely amazing! I think you are one of the most resourceful contributors in this forum to be able to spout out fixes and tweaks off the top of your head without the tools of a spreadsheet or VB editor to guide you. What a skill!

    Bhush,

    I'll take a look at it and attempt to follow Paul's lead to a resolution. Hang in there for a few.

    Maud

  19. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    bhushanvshah (2017-05-09),Paul T (2017-05-19)

  20. #14
    New Lounger
    Join Date
    Dec 2014
    Posts
    24
    Thanks
    8
    Thanked 1 Time in 1 Post
    Hi Paul & Maud,

    When i increase the number of websites in the array to 15 or more the program slows down drastically..

    could u please help me to tweak the code..
    so that instead of a For-Next loop for the 15 websites, all the 15 websites are scraped simultaneously..


    i got an interesting code through google on this topic.
    http://www.excelhero.com/blog/2010/0...eaded-vba.html

    thanks
    Bhushan

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
  •