Edgar Edgar - 2 months ago 20
Vb.net Question

Deserialize Google Financial JSON string

I am trying to Grab the latest stock prices from 3 companies and I cant seem to be able to get the numbers correctly. I can get the symbols I scrape google finance and I get a object similar to this:

// [ { "id": "983582" ,"t" : ".DJI" ,"e" : "INDEXDJX" ,"l" : "18,051.10" ,"l_fix" : "18051.10" ,"l_cur" : "18,051.10" ,"s": "0" ,"ltt":"3:15PM EDT" ,"lt" : "Sep 13, 3:15PM EDT" ,"lt_dts" : "2016-09-13T15:15:39Z" ,"c" : "-273.97" ,"c_fix" : "-273.97" ,"cp" : "-1.50" ,"cp_fix" : "-1.50" ,"ccol" : "chr" ,"pcls_fix" : "18325.07" } ]


and I'm trying to grab the item after the fourth colon, which is "18,051.10" and nullify the rest and put that double value into an array so I can return that and display to labels but have no luck. I'm a noob programmer trying to practice off a sample program that returned a stock graph history of a company but I just want to return latest quote.

Imports System.Net
Imports System.IO
Imports System.Drawing.Drawing2D

Public Class Form1
' The ticker symbols.
Private Symbols As List(Of String) = Nothing

' The current prices.
Private Prices() As List(Of Single) = Nothing

' Redraw the graph.
'Private Sub picGraph_Resize(ByVal sender As Object, ByVal e As System.EventArgs)
' DrawGraph()
'End Sub

' Get the closing prices and graph them.
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
Me.Cursor = Cursors.WaitCursor

' Get the ticker symbols.
'Dim symbols_text() As String = txtSymbols.Text.Split(","c)
Dim symbols_text() As String = {"PKG", "TEN", "INDU"}
Symbols = New List(Of String)()
For i As Integer = 0 To symbols_text.Length - 1
Symbols.Add(symbols_text(i).Trim())
Next i

' Get the data.
ReDim Prices(0 To Symbols.Count - 1)
For i As Integer = 0 To Symbols.Count - 1
Prices(i) = GetStockPrices(Symbols(i))
Next i

' Graph it.
'DrawGraph()
FillData()
Me.Cursor = Cursors.Default
End Sub

' Get the prices for this symbol.
'Private Function GetStockPrices(ByVal symbol As String) As List(Of Single)
' ' Compose the URL.
' Dim url As String = "http://www.google.com/finance/historical?output=csv&q=" & symbol

' ' Get the result.
' ' Get the web response.
' Dim result As String = GetWebResponse(url)

' ' Get the historical prices.
' Dim lines() As String = result.Split( _
' New String() {vbCr, vbLf}, _
' StringSplitOptions.RemoveEmptyEntries)
' Dim prices As New List(Of Single)()

' ' Process the lines, skipping the header.
' For i As Integer = 1 To lines.Length - 1
' Dim line As String = lines(i)
' prices.Add(Single.Parse(line.Split(","c)(4)))
' Next i

' Return prices
'End Function

Public Function GetStockPrices(symbol As String) As List(Of Single)
' Dim pricesArray() As String
Dim prices As New List(Of Single)()
Dim items() As GoogleFinanceItem

If UCase(symbol) = "INDU" Then
Dim url As String = "http://finance.google.com/finance/info?client=ig&q=INDEXDJX%3A.DJI"
Dim result As String = GetWebResponse(url)
' Dim newResult = result.Substring(1, result.Length - 1)
items = JsonConvert.DeserializeObject(Of GoogleFinanceItem())(result)
' pricesArray = Split(result, ":")
Else
Dim url As String = "http://finance.google.com/finance/info?client=ig&q=" & UCase(symbol)
Dim result As String = GetWebResponse(url)
' Dim newResult = result.Substring(1, result.Length - 1)
items = JsonConvert.DeserializeObject(Of GoogleFinanceItem())(result)
' pricesArray = Split(result, ":")
End If
' pricesArray = Split(pricesArray(4), """")

' prices.Add(CSng(Format(pricesArray(1), "0.00")))
'prices.Add(CSng(Format(pricesArray(1))))
prices.Add(CSng(items(0).price))
Return prices

End Function

' Get a web response.
Private Function GetWebResponse(ByVal url As String) As String
' Make a WebClient.
Dim web_client As New WebClient()

' Get the indicated URL.
Dim response As Stream = web_client.OpenRead(url)

' Read the result.
Using stream_reader As New StreamReader(response)
' Get the results.
Dim result As String = stream_reader.ReadToEnd()

' Close the stream reader and its underlying stream.
stream_reader.Close()

' Return the result.
Return result
End Using
End Function

Private Sub FillData()
Dim symbolSubSet() As Control = {Label1, Label2, Label3}
Dim priceSubSet() As Control = {Label4, Label5, Label6}

For i = 0 To 2
symbolSubSet(i).Text = Symbols(i)
priceSubSet(i).Text = Prices(i).ToString
Next
End Sub
' Draw the graph.
'Private Sub DrawGraph()
' If (Prices Is Nothing) Then Return

' ' Make the bitmap.
' Dim bm As New Bitmap( _
' picGraph.ClientSize.Width, _
' picGraph.ClientSize.Height)
' Using gr As Graphics = Graphics.FromImage(bm)
' gr.Clear(Color.White)
' gr.SmoothingMode = SmoothingMode.AntiAlias

' ' Get the largest prices.
' Dim max_price As Single = 10
' For Each symbol_prices As List(Of Single) In Prices
' Dim new_max As Single = symbol_prices.Max()
' If (max_price < new_max) Then max_price = new_max
' Next symbol_prices

' ' Scale and translate the graph.
' Dim scale_x As Single = -picGraph.ClientSize.Width / CSng(Prices(0).Count)
' Dim scale_y As Single = -picGraph.ClientSize.Height / max_price
' gr.ScaleTransform(scale_x, scale_y)
' gr.TranslateTransform( _
' picGraph.ClientSize.Width, _
' picGraph.ClientSize.Height, _
' System.Drawing.Drawing2D.MatrixOrder.Append)

' ' Draw the grid lines.
' Using string_format As New StringFormat()
' Using thin_pen As New Pen(Color.Gray, 0)
' For y As Integer = 0 To CInt(max_price) Step 10
' gr.DrawLine(thin_pen, 0, y, Prices(0).Count, y)
' Next y
' For x As Integer = 0 To Prices(0).Count - 1 Step 7
' gr.DrawLine(thin_pen, x, 0, x, 2)
' Next x
' End Using
' End Using

' ' Draw each symbol's prices.
' Dim colors() As Color = {Color.Black, Color.Red, Color.Green, Color.Blue, Color.Orange, Color.Purple}
' For symbol_num As Integer = 0 To Prices.Length - 1
' Dim symbol_prices As List(Of Single) = Prices(symbol_num)

' ' Make the data points.
' Dim points(0 To symbol_prices.Count - 1) As PointF
' For i As Integer = 0 To symbol_prices.Count - 1
' points(i) = New PointF(i, symbol_prices(i))
' Next i

' ' Draw the points.
' Dim clr As Color = colors(symbol_num Mod colors.Length)
' Using thin_pen As New Pen(clr, 0)
' gr.DrawLines(thin_pen, points)
' End Using

' ' Draw the symbol's name.
' DrawSymbolName(gr, Symbols(symbol_num), _
' symbol_prices(symbol_prices.Count - 1), clr)
' Next symbol_num
' End Using

' ' Display the result.
' picGraph.Image = bm
'End Sub

' Draw the text at the specified location.
'Private Sub DrawSymbolName(ByVal gr As Graphics, ByVal txt As String, ByVal y As Single, ByVal clr As Color)
' ' See where the point is in PictureBox coordinates.
' Dim old_transformation As Matrix = gr.Transform
' Dim pt() As PointF = {New PointF(0, y)}
' gr.Transform.TransformPoints(pt)

' ' Reset the transformation.
' gr.ResetTransform()

' ' Draw the text.
' Using small_font As New Font("Arial", 8)
' Using br As New SolidBrush(clr)
' gr.DrawString(txt, small_font, br, 0, pt(0).Y)
' End Using
' End Using

' ' Restore the original transformation.
' gr.Transform = old_transformation
'End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub
End Class


I also have the GoogleFinanceItem class

Imports Newtonsoft.Json


Public Class GoogleFinanceItem

Public Property id As String
Public Property t As String
Public Property e As String
Public Property l As String
<JsonProperty("l_fix")>
Public Property price As String
Public Property l_cur As String
Public Property s As String
Public Property ltt As String
Public Property lt As String
Public Property lt_dts As Date
Public Property c As String
Public Property c_fix As String
Public Property cp As String
Public Property cp_fix As String
Public Property ccol As String
Public Property pcls_fix As String


End Class


I am trying to deserialize the json and I am dumping the json string into results and passing that to the deserializer and getting the following error

An unhandled exception of type 'Newtonsoft.Json.JsonSerializationException' occurred in Newtonsoft.Json.dll

Additional information: Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'howto_net_graph_stock_history.GoogleFinanceItem[]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.

To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.

enter image description here

Answer

That is not just a string in a fancy format. It is a JSON string which means it can be parsed or deserialized into an object giving you easy access to all the information.

First, you want to get Newtonsoft's JSON parser. Then a class to capture the data:

' see this link for the meaning of the other properties
'https://www.quora.com/What-do-the-following-attributes-returned-in-a-JSON-array-from-the-Google-Finance-API-mean
Public Class GoogleFinanceItem
    Public Property id As String
    <JsonProperty("t")>
    Public Property Symbol As String
    <JsonProperty("e")>
    Public Property Index As String
    <JsonProperty("l")>
    Public Property LastTradePrice As Decimal
    <JsonProperty("l_fix")>
    Public Property LastPriceCurrency As Decimal
    Public Property l_cur As String
    Public Property s As String
    Public Property ltt As String
    Public Property lt As String
    Public Property lt_dts As Date
    <JsonProperty("c")>
    Public Property Change As String
    Public Property c_fix As String
    Public Property cp As String
    Public Property cp_fix As Decimal
    Public Property ccol As String
    Public Property pcls_fix As String
End Class

You can auto generate the class this way:

  1. Copy the json to the clipboard
  2. Create a new Class file
  3. Select Edit | Paste Special | Paste JSON As Classes

If you have a much older version of Visual Studio which doesn't have that, you can use the robot at http://jsonutils.com/ to create VB or C# classes.

I made a few changes to the class proposed by the robot. a) Those are pretty grody property names. To convert them to something more descriptive or readable, you can use <JsonProperty("json name")> to tell JSON.NET to deserialize into a different property name. b) I also changed the numerics to Decimal, so they can be used in math elsewhere.

Deserializing is easy, but there is an extra clean up step. Google apparently would prefer that you use their API rather than use the response yourself, so there is a leading "//" in it. This needs to be trimmed off before it will deserialize:

Dim jstr = GetWebResponse(url).Replace("//", "")

Dim items As GoogleFinanceItem() = JsonConvert.DeserializeObject(Of GoogleFinanceItem())(jstr)

In this case it is an array of one, and it is not clear if/how to get multiple results in one call (their API again?). But the data will be available via the properties:

Console.Writeline(items(0).Ticker))

Using a property grid to view the object's property values:

enter image description here


Future readers may want to get multiple quotes into a list for a DGV:

Dim syms As String() = {"GOOG", "MSFT", "INTC"}
GQuotes = New List(Of GoogleFinanceItem)

Dim urlMask As String = "http://finance.google.com/finance/info?client=ig&q={0}"
Dim url As String

For Each s As String In syms
    url = String.Format(urlMask, s)
    Dim jstr As String = GetWebResponse(url).Replace("//", "")
    Dim items = JsonConvert.DeserializeObject(Of GoogleFinanceItem())(jstr)

    GQuotes.Add(items(0))
Next

dgv1.DataSource = GQuotes

If the list is long, it may be necessary to pause briefly between requests to prevent posting too many requests in too short of time.