Formatting Web Queries into a Table Automatically

[butterdutter]

Hello,

I am trying to set up an investment tracker for stocks and I am pulling in data from a web query but it comes in as a long string. For example,

{"high":"56.36","last":"52.44","timestamp":"1505404209","volume":"4516.96093488","vwap":"54.54156896","low":"49.30","ask":"49.00","bid":"55.00"}

How would I be able to automatically format the string into a clean table? It would have to do so upon each refresh as well. For example,

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @Page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;} td {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style>[TABLE="width: 110"]
<!--StartFragment--> <colgroup><col width="55" span="2" style="width:55pt"> </colgroup><tbody>[TR]
[TD="width: 55"]Column1[/TD]
[TD="width: 55"] Column2[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD="align: right"]56.36[/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD="align: right"]52.44[/TD]
[/TR]
[TR]
[TD]Timestamp[/TD]
[TD="align: right"] 150540429[/TD]
[/TR]
[TR]
[TD]Volume[/TD]
[TD="align: right"]4516.35[/TD]
[/TR]
[TR]
[TD]vwap[/TD]
[TD="align: right"]54.54[/TD]
[/TR]
[TR]
[TD]Low[/TD]
[TD="align: right"]49.3[/TD]
[/TR]
[TR]
[TD]Ask[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]Bid[/TD]
[TD="align: right"]55[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

I would then use a VLOOKUP to pull the data into the actual investment tracker sheet I have created. Any suggestions would be greatly appreciated.

Thanks,

butterdutter

 

[butterdutter]

I recorded this macro to parse out the string but it's clearly inefficient and could be greatly improved.

Anyone have suggestions on how to improve the code and make it more efficient.... ALSO this recorded macro is completely separate from web query but ideally one macro to bring it all in and format into a table would be ideal. See below for code:

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00 ; background-color: #ffffff } p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>Sub autoFormat()


'
' Keyboard Shortcut: Option+Cmd+g
'
Application.CutCopyMode = False
Range("A1").TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _
, Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1))
Range("C3:D3").Select
Selection.Cut
Range("A4").Select
ActiveSheet.Paste
Range("E3:F3").Select
Selection.Cut
Range("A5").Select
ActiveSheet.Paste
Range("G3:H3").Select
Selection.Cut
Range("A6").Select
ActiveSheet.Paste
Range("I3:J3").Select
Selection.Cut
Range("A7").Select
ActiveSheet.Paste
Range("K3:L3").Select
Selection.Cut
Range("A8").Select
ActiveSheet.Paste
Range("M3:N3").Select
Selection.Cut
Range("A9").Select
ActiveSheet.Paste
Range("O3:P3").Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
End Sub