Download Json Data to excel

Topic

New Reply

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

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

and want to pull the above data every 30 seconds.

thanks in advance,
Bhushan

Reply | Quote

Replies

Here are some examples to get you started.
http://www.excely.com/template/stock-quotes-data-from-google-finance.shtml
http://investexcel.net/google-finance-stock-quotes-in-excel/
http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula

cheers, Paul

Reply | Quote

Thanks for your reply.. But i intend to work on Json Data…

which is different from the examples given in the links.

Regards
bhushan

Here are some examples to get you started.
http://www.excely.com/template/stock-quotes-data-from-google-finance.shtml
http://investexcel.net/google-finance-stock-quotes-in-excel/
http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula

cheers, Paul

Reply | Quote

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

Reply | Quote

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

45924-Bhush1

Code:

Sub WebData()
[COLOR=”#008000″]’—————————
‘DECLARE AND SET VARIABLES[/COLOR]
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”)
[COLOR=”#008000″]’—————————
‘GET WEB DATA[/COLOR]
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
[COLOR=”#008000″][/COLOR][COLOR=”#008000″]’—————————
‘FORMAT DATA[/COLOR]
    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)
[COLOR=”#008000″]’—————————
‘FORMAT DATA[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
For I = LastRow To 1 Step -1
[COLOR=”#008000″]’—————————
‘REMOVE UNWANTED CHARACTERS[/COLOR]
    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
[COLOR=”#008000″]’—————————
‘DELETE BLANK ROWS[/COLOR]
    If Cells(I, col) = “” Or Cells(I, col) = ” ” Then
        Cells(I, col).EntireRow.Delete
[COLOR=”#008000″][/COLOR][COLOR=”#008000″]’—————————
‘SPLIT DATA INTO COLUMNS[/COLOR]
    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

Reply | Quote

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

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

45924-Bhush1

Code:

Sub WebData()
[COLOR=#008000]’—————————
‘DECLARE AND SET VARIABLES[/COLOR]
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”)
[COLOR=#008000]’—————————
‘GET WEB DATA[/COLOR]
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
[COLOR=#008000]’—————————
‘FORMAT DATA[/COLOR]
    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)
[COLOR=#008000]’—————————
‘FORMAT DATA[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
For I = LastRow To 1 Step -1
[COLOR=#008000]’—————————
‘REMOVE UNWANTED CHARACTERS[/COLOR]
    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
[COLOR=#008000]’—————————
‘DELETE BLANK ROWS[/COLOR]
    If Cells(I, col) = “” Or Cells(I, col) = ” ” Then
        Cells(I, col).EntireRow.Delete
[COLOR=#008000]’—————————
‘SPLIT DATA INTO COLUMNS[/COLOR]
    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

Reply | Quote

Maud to the rescue, as usual. 🙂

cheers, Paul

Reply | Quote

Thank you Maud, It works beautiful… and the format is excellent … cheers and thanks once again..
bhushan

Reply | Quote

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:JETAIRWAYS”, _
“http://finance.google.com/finance/info?client=ig&q=NSE:INDUSINDBK,NSE:NAUKRI,NSE:INFY”)

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

Reply | Quote

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

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:JETAIRWAYS”, _
“http://finance.google.com/finance/info?client=ig&q=NSE:INDUSINDBK,NSE:NAUKRI,NSE:INFY”)

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

Reply | Quote

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

Reply | Quote

hi maud.. could you help out on this glitch..

thanks
Bhushan

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

Reply | Quote

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

Reply | Quote

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/05/multi-threaded-vba.html

thanks
Bhushan

Reply | Quote