0

I have API which gives me the stock price based on constructed URL with token and symbol,this API returns is web based and returns XML/JSON format data in <root></root> and data in it. Below is an example.

 <root>
     <n5323 type="float">31.35</n5323 >
     <n3221 type="float">9.06</n3221 >
 </root>

I would like to retrieve price in Excel using VBA (31.35 and 9.06), please help me on this how to achieve. Not that the n5323 and n3221 are generated dynamically and are different every time.

My VBA code is as below

Dim strXMLSite As String
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim objXMLDoc As MSXML2.DOMDocument
Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode
Set objXMLHTTP = New MSXML2.XMLHTTP
Set objXMLDoc = New MSXML2.DOMDocument

strXMLSite = "http://testserver/volume/daily?token=' + token$ + '&ticker='+Symbol$+'"

objXMLHTTP.Open "POST", strXMLSite, False
objXMLHTTP.send
objXMLDoc.LoadXML (objXMLHTTP.responseText)

Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("root")
Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode("root")

Worksheets("Sheet1").Range("J1").Value = objXMLNodeDIIRSP.Text
Community
  • 1
  • 1
user5769212
  • 27
  • 3
  • 8
  • Add reference to `Microsoft XML, v6.0`, instantiate a `DOMDocument` and `loadXML` the response. – GSerg Mar 06 '16 at 08:33
  • could you please put code snipet here ? I have updated my code in main post... – user5769212 Mar 06 '16 at 08:34
  • Possible duplicate of [MSXML “The system cannot locate the resource specified”](http://stackoverflow.com/q/4041687/11683) – GSerg Mar 06 '16 at 09:07
  • I got the response using my code above in orignial post in xmldoc but can any body tell me how to parse this from response , below is the xml response I wan to parse and this n321 and n314 are generated randomely. 31.35 9.06 – user5769212 Mar 06 '16 at 18:42
  • Try to replace the string `"http://testserver/volume/daily?token=' + token$ + '&ticker='+Symbol$+'"` with `"http://testserver/volume/daily?token=" & token$ & "&ticker=" & Symbol$`, also make sure that the strings in `token` and `Symbol` variables are URL-encoded. – omegastripes Mar 06 '16 at 23:32
  • It is already working , my now question is how to parse XML response out of that, please see above post I pasted XML response which needs to be parsed. – user5769212 Mar 07 '16 at 02:59

1 Answers1

0

If you don't know what node names there are, you enumerate nodes.

Dim n As IXMLDOMNode
For Each n In objXMLDoc.documentElement.childNodes
  Debug.Print n.nodeName, n.Text
Next
GSerg
  • 76,472
  • 17
  • 159
  • 346