Jump to content

Trouble parsing json with jsonGetElement


Recommended Posts

Hi Folks,

New here and new to FM, but I'm making some good headway.. I've made my way into accessing stock quotes via one of the brokers API's and been successful parsing out the response for a straight stock quote.  I'm now on to options quotes, which has a more complex json response... I've tried using generator and generator seems to stuck as well with a "?" in the field mapping for a key portion of the response.  Here's the complete body:

{
  "underlying": {
    "symbol": "KO",
    "bidSize": 700,
    "bid": 52.5,
    "close": 54.84,
    "mark": 52.51,
    "quoteTime": 1609787762126,
    "fiftyTwoWeekLow": 36.27,
    "description": "Coca-Cola Company (The) Common Stock",
    "change": -2.34,
    "percentChange": -4.26,
    "last": 52.51,
    "markPercentChange": -4.26,
    "markChange": -2.34,
    "ask": 52.51,
    "tradeTime": 1609787762126,
    "askSize": 400,
    "highPrice": 54.63,
    "totalVolume": 15968734,
    "lowPrice": 52.03,
    "openPrice": 54.27,
    "exchangeName": "NYS",
    "fiftyTwoWeekHigh": 60.13,
    "delayed": true
  },
  "isDelayed": true,
  "isIndex": false,
  "volatility": 29,
  "daysToExpiration": 0,
  "symbol": "KO",
  "interval": 0,
  "status": "SUCCESS",
  "strategy": "SINGLE",
  "interestRate": 0.1,
  "underlyingPrice": 52.474999999999994,
  "numberOfContracts": 1,
  "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {
          "symbol": "KO_021921P52.5",
          "multiplier": 100,
          "bid": 1.84,
          "openInterest": 4683,
          "bidSize": 175,
          "highPrice": 2.07,
          "totalVolume": 208,
          "delta": -0.479,
          "rho": -0.033,
          "theoreticalVolatility": 29,
          "putCall": "PUT",
          "volatility": 25.33,
          "description": "KO Feb 19 2021 52.5 Put",
          "quoteTimeInLong": 1609788662180,
          "askSize": 37,
          "lastSize": 0,
          "exchangeName": "OPR",
          "mark": 1.88,
          "inTheMoney": true,
          "tradeDate": null,
          "ask": 1.91,
          "last": 1.87,
          "closePrice": 0.93,
          "settlementType": " ",
          "bidAskSize": "175X37",
          "netChange": 0.94,
          "lowPrice": 0.97,
          "openPrice": 0,
          "tradeTimeInLong": 1609787638288,
          "nonStandard": false,
          "gamma": 0.084,
          "timeValue": 1.85,
          "expirationType": "R",
          "deliverableNote": "",
          "theta": -0.02,
          "vega": 0.075,
          "theoreticalOptionValue": 1.875,
          "strikePrice": 52.5,
          "optionDeliverablesList": null,
          "expirationDate": 1613768400000,
          "daysToExpiration": 46,
          "lastTradingDay": 1613782800000,
          "isIndexOption": null,
          "percentChange": 102.16,
          "markChange": 0.95,
          "markPercentChange": 102.7,
          "mini": false
        }
      ]
    }
  },
  "callExpDateMap": {}
}

I'm only looking to retrieve the "mark" value at this point.. in this case "1.88".   Here's my attempt to retrieve the element:

JSONGetElement( $json ; "$instrument&ExpDateMap.&$expiration&:46.&$strike&.mark" )

Alas, I'm unable to retrieve the "mark" value to the variable successfully using this syntax.  It seems that the issue lies in this section of the response:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {

To complicate things there are several variables here that need to be dealt with... An expiration date:  "2021-02-19" and a price: "52.5".  I'm not sure what the ":46" is since it's a future data.. (??), so I'm just inserting that into the get element request as is for testing purposes.  I don't get an error when evaluating this via script debugger and data viewer, but I don't get a result either...  As I said, Generator can't seem to parse out the fields either... no elements below the section above are being parsed in Generator. 

JSON PATH				VALUE
putExpDateMap.2021-02-19:46.52.5         ?

I'm stumped.. much appreciation if anyone can point me in the right direction.

John

Link to post
Share on other sites

I believe it should be:

JSONGetElement( $json ; 
["putExpDateMap.mark"];
["putExpDateMap.expiration"];
["putExpDateMap.strike"]
)

 

Link to post
Share on other sites

You're going to have a hard time with this because keys have a period in the json object "52.5" fmp can't handle json key's with periods. 

Link to post
Share on other sites

Thanks Agnes.

I think I was a bit unclear above.  In my attempt above: 

JSONGetElement( $json ; "$instrument&ExpDateMap.&$expiration&:46.&$strike&.mark" )

I neglected to detail out the variables I'm inserting into the request.  These are:

$instrument = call or put ("put" in this case..)

$expiration = expiration date ("2021-02-19" in this case)

$strike = strike price ("52.5")

These are used in an attempt to traverse down through:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {

To get to:

 "mark": 1.88,

  I don't think what you've suggested will work.  Could you take another look?

 

Thanks!

 

John

Link to post
Share on other sites
Posted (edited)

Ocean West,

Thanks for the feedback...  even though the "52.5" is a variable and inserted based on the set variable, "$strike"?  

 

John

 

Edited by jbmia
Link to post
Share on other sites

I thought you are getting info from JSON, not setting it.

Sorry, I totally missed that your key contains a period.

Link to post
Share on other sites

Haha... I am getting from JSON.. I'm just trying to get the element "mark" from the response above...  My issue is how to get down through this piece to "mark" to retrieve it:

 "putExpDateMap": {
    "2021-02-19:46": {
      "52.5": [
        {
		...
		"mark": 1.88,

It seems this structure is composed of variables  and might look something like this...   "putExpDateMap.$date.$strike.mark, where,

$date = 2021-02-19

$strike = 52.5

I did find a custom function, Parsing JSON with challenging "dot" keys (claris.com), that I'll give a try as a work around for the "dot" in 52.5

I appreciate any further insight anyone might have... 

Thanks again!

John

 

 

Link to post
Share on other sites

Pretty ugly but...

Let ( [


a = $JSON ;
b = JSONGetElement ( a ; "putExpDateMap" ) ;
c = JSONListKeys ( b ; "" ) ;
d = Position ( a ; c ; 1 ; 1 ) ;
e = Replace ( a ; d ; Length(c) ; "MYKEY" ) ;
f = JSONGetElement ( e ; "putExpDateMap.MYKEY" ) ;
g = JSONListKeys ( f ; "" ) ;
h = Position ( f ; g ; 1 ; 1 ) ;
i = Replace ( f ; h ; Length(g) ; "MYKEY" ) ;

z = "" 

];

JSONGetElement ( i ; "MYKEY[0].mark" )

)

 

  • Like 1
Link to post
Share on other sites
Posted (edited)

Looks a lot better than I could come up with... i'm a newb w/ scripting but can see where you're going with it...  Many thanks!

Here's what I get when I attempt to run the script: 

 ? * Line 1, Column 1 Syntax error: value, object or array expected.

I've attached a copy of the script itself in hopes there's something obvious I've done wrong...   the json response detailed above is the result of the "Insert from URL" step below.

Many thanks again for any assistance!!

John

 

script_capture.PNG

Edited by jbmia
Link to post
Share on other sites

I would put that JSOn in a global variable ($$JSON) and use the DataViewer to play with it until you get it right. Break down each row and see what it gives back.

  • Like 1
Link to post
Share on other sites

First of all, there is nothing wrong with your JSON. The problem is with the library Claris are using to parse the JSON, or with their implementation of it.

Now, if you know in advance the path to the value you want, you could do simply something like:

Let ([
myJSON = Substitute ( $json ; "\"52.5\"" ; "\"52§5\"" ) ;
myKey = "putExpDateMap.2021-02-19:46.52§5[0].mark"
] ;
JSONGetElement ( myJSON ; myKey )
)

If you are assembling the path from variables, take care to get the exact path as shown above. Note that the value of the "52.5" key is an array; at least in theory it could contain more than one object, and thus more than one "mark" value.

 

  • Like 2
Link to post
Share on other sites

Circling back around on this, I wanted to thank everyone who's contributed so far.  It appears I still have some work to do on this one... I'm thinking perhaps I might look for an API that's a bit easier to navigate rather than jump through the hoops mentioned here.   In the meantime huge thanks to you guys for taking time out of your day to respond to my questions.  😉

Link to post
Share on other sites

Thanks for the follow up...  the challenge I'm having is that the path is dynamic and will need to be navigated based on submitting each variable:

putExpDateMap.2021-02-19:46.52§5[
  • 2021-02-19 is the "expiration date", no issue building this.
  • ":46" , I believe that this variable is the number of days between today and the expiration date... that shouldn't be too hard to calculate and insert into the request. 😉 
  • Then there's the "52.5" which is the strike price..the only issue here being that "dot".  Your solution looks like it would work, but I'd need to break down the price variable into the integer portion, "52" and the decimal portion, the ".5" in the case.. The integer portion could be 1, 2, 3, or even 4 digits in length and the decimal portion may be 1 or 2 or maybe just a "0" value.  I'd then need to concatenate the integer portion, your section symbol, and the decimal portion based on the current requested symbol strike in order to execute your command...

Any further tips would be appreciated..  I'm still new to this ...

John 

Link to post
Share on other sites

Suppose you have already defined these variables:

$instrument = "put" ;
$expiration = "2021-02-19" ;
$days = 46 ;
$strike = 52.5 ;

then you can modify the above calculation to:

Let ([
newStrike = Substitute ( $strike ; "." ; "§" ) ;
myJSON = Substitute ( $json ; Quote ( $strike ) ; Quote ( newStrike ) ) ;
myKey = $instrument & "ExpDateMap." & $expiration & ":" & $days & "." & newStrike & "[0].mark"
] ;
JSONGetElement ( myJSON ; myKey )
)


Demo file attached.

 

DEMOParseJSONwDot.fmp12

Edited by comment
  • Thanks 1
Link to post
Share on other sites

perhaps use a web viewer with some javascript that uses a different JSON parsing library that can manage paths with characters that FileMaker can't.

Link to post
Share on other sites

Wow.  You guys are are awesome!  Thanks comment for the demo file!  I'm going to get on this today.

John

Link to post
Share on other sites
Posted (edited)

Comment,

Your are the man!  I was able to get this working using your example.  Many thanks for going out of your way to solve this problem and build the demo file.  It was very helpful  Thank you so much!  

There were a couple additional wrinkles I had to solve: (1)  data provider wants a whole number strike price as a single decimal (e.g., $65 must be 65.0) so I found a custom function, "NumberFormat", and converted the strike price before setting the $strike variable.  (2) the instrument had to be sent as an upper (CALL, PUT), yet the JSON response is lower (call, put), so set variables for each.  Upper for the request, and lower for parsing the response.

In the interest of sharing (I couldn't find another parse example like this...), given those who have shared with me, 🙂, following is the complete script for reference purposes.  No doubt there could be improvements, but it does work (at least based on preliminary tests).  Feel free to suggest improvements...

Thanks again to everyone who jumped in to assist!

John

#Options Prices: updateMarketPrice[Options Prices]
#SCRIPT: updateMarketPrice[Options Prices] used to retrieve options chain and parse quotes and other data into Option Prices table.

#____________________________________________________________
#Set variables for underlying symbol, instrument (e.g., stock, call, put, etc), strike price, and expiration date
Set Variable [ $underlying; Value:Options Prices::underlying ]
Set Variable [ $upperInstrument; Value:Options Prices::upperInstrument ]
Set Variable [ $lowerInstrument; Value:Options Prices::lowerInstrument ]
Set Variable [ $strike; Value:Options Prices::DecimalStrike ]
Set Variable [ $expiration; Value:Options Prices::expDateFormatted ]
Set Variable [ $DaysToExpir; Value:Options Prices::Number Days to Expiration ]

#___________________________________________________________
#curl request via Insert From URL command.
Insert from URL [ $json2; "https://api."XXX".com/v1/marketdata/chains? apikey="XXX"&symbol="& $underlying &"&contractType="& $upperInstrument &"&includeQuotes=TRUE&strategy=SINGLE&strike="& $strike &"&fromDate="&$expiration &"&toDate="& $expiration & ""; cURL options: "cURL options: -X GET --header Authorization: --show-error --dump-header $dump" ]
[ Select; No dialog ]

#Switch "dots" for "sections" and save complete JSON response (for testing)
Set Variable [ $newStrike; Value:Substitute ( $strike ; "." ; "§" ) ]
Set Variable [ $myJSON; Value:Substitute ( $json2 ; Quote ( $strike ) ; Quote ( $newStrike ) ) ] 
Set Field [ Options Prices::JSON; $myJSON ]

#____________________________________________________________
#BUILD KEYS TO RETRIEVE VARIABLES
Set Variable [ $myMark; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. mark" ]
Set Variable [ $mySymbol; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. symbol" ]
Set Variable [ $myAsk; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].ask" ]
Set Variable [ $myBid; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].bid" ]
Set Variable [ $myLast; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].last" ]

#_____________________________________________________________ 
#RETRIEVE VARIABLES
Set Variable [ $mark; Value:JSONGetElement ( $myJSON ; $myMark ) ]
Set Variable [ $symb; Value:JSONGetElement ( $myJSON ; $mySymbol) ] 
Set Variable [ $ask; Value:JSONGetElement ( $myJSON ; $myAsk) ]
Set Variable [ $last; Value:JSONGetElement ( $myJSON ; $myLast) ] 
Set Variable [ $bid; Value:JSONGetElement ( $myJSON ; $myBid) ]

#————————————————————————————————————————————————————————— —————
#SET VARIABLES INTO FIELDS
Set Field [ Options Prices::market price; $mark ] 
Set Field [ Options Prices::Symbol.; $symb ] 
Set Field [ Options Prices::Ask; $ask ]
Set Field [ Options Prices::Bid; $bid ]
Set Field [ Options Prices::Last; $last ]

 

Edited by jbmia
Link to post
Share on other sites
3 hours ago, jbmia said:

No doubt there could be improvements

There are couple of ways this could be streamlined. Some of these are cosmetic, but one practically screams off the page:

All of your values are retrieved from the same branch of the JSON. Instead of repeatedly parsing the entire tree for each individual value, you should isolate the target branch in a variable once, then parse out the individual values from this variable.

IOW, instead of:

#____________________________________________________________
#BUILD KEYS TO RETRIEVE VARIABLES
Set Variable [ $myMark; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. mark" ]
Set Variable [ $mySymbol; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]. symbol" ]
Set Variable [ $myAsk; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].ask" ]
Set Variable [ $myBid; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].bid" ]
Set Variable [ $myLast; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0].last" ]

#_____________________________________________________________ 
#RETRIEVE VARIABLES
Set Variable [ $mark; Value:JSONGetElement ( $myJSON ; $myMark ) ]
Set Variable [ $symb; Value:JSONGetElement ( $myJSON ; $mySymbol) ] 
Set Variable [ $ask; Value:JSONGetElement ( $myJSON ; $myAsk) ]
Set Variable [ $last; Value:JSONGetElement ( $myJSON ; $myLast) ] 
Set Variable [ $bid; Value:JSONGetElement ( $myJSON ; $myBid) ]

#————————————————————————————————————————————————————————— —————
#SET VARIABLES INTO FIELDS
Set Field [ Options Prices::market price; $mark ] 
Set Field [ Options Prices::Symbol.; $symb ] 
Set Field [ Options Prices::Ask; $ask ]
Set Field [ Options Prices::Bid; $bid ]
Set Field [ Options Prices::Last; $last ]

you would have something like (untested):

Set Variable [ $myKey; Value:$lowerInstrument & "ExpDateMap." & $expiration & ":" & $DaysToExpir & "." & $newStrike & ".[0]" ]
Set Variable [ $myBranch; Value:JSONGetElement ( $myJSON ; $myKey ) ]

#————————————————————————————————————————————————————————— —————
#POPULATE FIELDS
Set Field [ Options Prices::market price; JSONGetElement ( $myBranch ; "mark" ) ] 
Set Field [ Options Prices::Symbol.; JSONGetElement ( $myBranch ; "symbol" ) ] 
Set Field [ Options Prices::Ask; JSONGetElement ( $myBranch ; "ask" ) ] 
Set Field [ Options Prices::Bid; JSONGetElement ( $myBranch ; "bid" ) ] 
Set Field [ Options Prices::Last; JSONGetElement ( $myBranch ; "last" ) ] 

 

 

Edited by comment
  • Thanks 1
Link to post
Share on other sites

Thanks !!   Will update on my side.  Again, much appreciation for the assistance.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.