JSON Historical quotes ends with :: Text could not be parsed at index 0

jonathlan · November 8, 2020, 11:21am

Hi there!

Let me start saying that I love your product, you’re doing quite an amazing job here and I really appreciate it, thank you very very much!

I’m facing a little problem while trying to use data from a JSON API in the instruments view, it’s telling me that “Text ‘06/11/2020’ could not be parsed at index 0”

Those are the steps to reproduce:

Go to All securities > Create new instrument
Enter required data and go to “Historical Quotes”
Enter below values:

Provider: JSON
Feed URL: https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos/oportuno?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64
Path do date: $.bmx[].[].datos[*].fecha
Path to close: $.bmx[].[].datos[*].dato

Check the result:

The Date field shows “Text ‘06/11/2020’ could not be parsed at index 0”

The above call will provide the latest price of the security in JSON format as below:

{"bmx":{"series":[{"idSerie":"SF61785","titulo":"Udibonos (Inflation indexed bonds) 3 years - Clean price","datos":[{"fecha":"06/11/2020","dato":"706.231225"}]}]}}

I tested my paths using “http://jsonpath.herokuapp.com/” and they seem to be good:

Date:

Close

For this case I was expecting to see at least one record containing the date and value of the security in Historical Data similarly to below picture:

Same “Text could not be parsed at index 0” happens using below address, which provides historical data:
https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64

Of course, the paths provide an array of dates and values respectively

Path do date: $.bmx[].[].datos[*].fecha
Path to close: $.bmx[].[].datos[*].dato

I also tried using DATE macros, but didn’t work either:
https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos/{DATE:yyyy-MM-01}/{DATE:yyyy-MM-10}?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64

In the forum I found another 3 similar questions but quite not the same:

I am able to open my portfolio

This seems to be related to XML data file

Solved on Jan-2020 but I’m already using latest version (0.49.1)

I feel like I’m doing something wrong, but couldn’t figure out what it is just yet.
Could you please help with some ideas?

Those are some details about my setup:
PP Version: 0.49.1 (Nov 2020)
OS: Windows 10 Version 2004 x64

Thank you!

chirlu · November 8, 2020, 1:32pm

It seems only the ISO date format is supported, i.e. 2020-11-06.

jonathlan · November 8, 2020, 1:55pm

I was afraid of something like that, I will probably need to setup a proxy then.
Thank you!

chirlu · November 8, 2020, 2:02pm

I haven’t tried it, but if functions are supported, something like this might work: concat(substring ($.bmx[*].[*].datos[*].fecha, 7, 4), '-', ...)

jonathlan · November 8, 2020, 2:31pm

Thanks,
I tried a couple things but seems that only contact is accepted. substring and substr are not.

ProgFriese · November 10, 2020, 9:49pm

I am not completely sure, but you might have another problem too.

curl -D header.txt "https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos/oportuno?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64"
{"bmx":{"series":[{"idSerie":"SF61785","titulo":"Udibonos (Inflation indexed bonds) 3 years - Clean price","datos":[{"fecha":"09/11/2020","dato":"705.490360"}]}]}}

cat header.txt 
HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: Bmx-Token, Accept, Accept-Encoding, Content-Type
Access-Control-Allow-Methods: GET, OPTIONS
Set-Cookie: JSESSIONID=3eb40ba9f179b744bc23be9db70f; Path=/SieAPIRest; HttpOnly
Cache-Control: no-store
Content-Language: en
Content-Type: application/json;charset=UTF-8
Date: Tue, 10 Nov 2020 20:48:59 GMT
Set-Cookie: ser9108090=3343861418.39455.0000; path=/; Httponly
Set-Cookie: TS0175f232=0189f484af96cdc687193f50435e444851dde039d1a6fd72406dccf39b745a0292b36aec7366a138928c400eecbd76741e66dbdce478554a1c737ea58f79b33b9e4878b09c; Path=/
Set-Cookie: TS01663c44=0189f484afe2f3a369fd4793749af2468477867427a6fd72406dccf39b745a0292b36aec73c717cbf1c5a78c91efd22a07eb1659c0f998443f5fb12382680054ab31335e3d; path=/SieAPIRest
Transfer-Encoding: chunked

The host www.banxico.org.mx sends “Content-Type: application/json;charset=UTF-8” (that’s fine) without a “Content-length”-Header (that’s not fine). I don’t know if PP can handle this - some applications are confused by this problem.

As I have said in JSON-Kursdaten ohne Datum - #4 by ProgFriese → proxy written in GnuAWK (for Linux):

cat proxy.awk 
BEGIN {
	RS = ORS = "\r\n"
	# /net-type/protocol/localport/hostname/remoteport
	HttpService = "/inet/tcp/8081/0/0"

	while(1) {
		# wait for new client request
		HttpService |& getline

		url="https:/"$2
		geturl="curl -H 'Cache-Control: no-cache' --no-sessionid \""url"\""
		geturl | getline body

		body_new = gensub(/([0-9][0-9])\/([0-9][0-9])\/([0-9][0-9][0-9][0-9])/, "\\3-\\2-\\1", "g", body)

		print "HTTP/1.1 200 OK"					|& HttpService
		print "Connection: Close"            			|& HttpService
		print "Pragma: no-cache"             			|& HttpService
		print "Content-Type: application/json;charset=UTF-8"    |& HttpService
		len = length(body_new) + length(ORS)
		print "Content-length:", len         			|& HttpService
		print ORS body_new                     			|& HttpService

		# ignore all the header lines
		while ((HttpService |& getline) > 0)
        		continue
		# stop talking to this client
		close(HttpService)
	}
}

This is a very rudimentary (no multi-threading) proxy that changes the date-format and also adds the header “Content-length”. You can start the service with

gawk -f proxy.awk

then the proxy is listening

 sudo netstat -tanp
[sudo] Passwort für mj: 
Aktive Internetverbindungen (Server und stehende Verbindungen)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:631             0.0.0.0:*               LISTEN      3318/cupsd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2059/master         
tcp        0      0 0.0.0.0:8081            0.0.0.0:*               LISTEN      9996/gawk     <------------- look here         
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      807/systemd-resolve 
tcp        0      0 192.168.2.104:34228     34.209.161.31:443       VERBUNDEN   5283/firefox        
tcp6       0      0 :::631                  :::*                    LISTEN      3318/cupsd          
tcp6       0      0 ::1:25                  :::*                    LISTEN      2059/master         
tcp6       0      0 2003:cf:8706:ba13:47080 2a03:4000:9:38a::1:443  VERBUNDEN   5283/firefox

If you now ask banxico.org.mx over the proxy, it looks like

I have not test the result in PP but I’m confident

Problem: You are running Windows and the Win32 port has some limitations, in particular the ‘|&’ operator and TCP/IP networking are not supported.

So, all in all it’s just an idea - but maybe you have a raspi?

If you find (or have found already) a solution for windows - please post it here.

jonathlan · November 14, 2020, 11:52pm

Thanks ProgFriese,

Unfortunatelly I don’t have any linux system.
I sill can download the data in csv but I wanted to skip that manual step.

Will let you know in case I get another solution.
Thanks again!

jonathlan · November 19, 2020, 11:42pm

Hello again,

I almost achieved this with a small app in Ruby, but now I’m facing another strange problem:

Using latest price url through my proxy, now PP is properly handling the data

url:http://localhost:4567/format?url=https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos/oportuno?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64

However when using historial data, PP does not show any of the values

URL:http://localhost:4567/format?url=https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64

From the results I can see that date is now in correct format and whith proper headers:

This is my app if you feel curious:

# myapp.rb
require 'sinatra'
require 'open-uri'
# Historical
# http://localhost:4567/format?url=https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64
# Latest
# http://localhost:4567/format?url=https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos/oportuno?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64

get '/' do
    "Welcome!"
end

get '/format' do    
    # matches "GET /format?url=foo&token=bar"
    ORS = "\r\n"
    content_type = ''
    targeturl = params['url']
    params.each { |key, value|         
        targeturl << "&#{key}=#{value}" if key != 'url'
    }
    
    body = open(targeturl) {|f|
    content_type = f.content_type
    f.read
    }    
    body.gsub!(/([0-9][0-9])\/([0-9][0-9])\/([0-9][0-9][0-9][0-9])/, "\\3-\\2-\\1")
    body << ORS
    type = "#{content_type}; charset = UTF-8"
    halt 200, {'Content-Type' => type, 'Content-length' => body.length}, body
end

Strange it works with single value but not multiple, isn’t it?
Is there anything else I’m missing?

Thanks for the support!

chirlu · November 20, 2020, 3:12am

Are you trying both at the same time?

The same applies for the URL fields.

jonathlan · November 21, 2020, 4:01pm

Thanks Chirlu,

I’m not using the ‚Lastest qoute‘ tab, that’s set to „(same as historical quotes)“

I’m only using „Historical Quotes“ instead.
Interestingly, when I set the same path in both „Path to date“ and „Path to close“ quotes are being filled in, but with wrong data. Qoute value is showing the year instead of the actual quote.

Path to date $.bmx[*].[*].datos[*].fecha
Path to close $.bmx[*].[*].datos[*].fecha

This is wrong of course, but using correct paths it doesn’t seem to get anything.

Path to date $.bmx[*].[*].datos[*].fecha
Path to close $.bmx[*].[*].datos[*].dato

What’s the correct way to do it?

Thank you!

Ragas · November 21, 2020, 4:25pm

Did you have checked the latest output from your local host end point? Could you post sampling data?

jonathlan · November 21, 2020, 7:48pm

Hi Ragas,

Of course, I posted a picture, but complete result is in the attachement "ProxyResults_20201121.zip (29.3 KB) "

This is a portion of it:

{
“bmx”: {
“series”: [
{
“idSerie”: “SF61785”,
“titulo”: “Udibonos (Inflation indexed bonds) 3 years - Clean price”,
“datos”: [
{
“fecha”: “2007-10-04”,
“dato”: “391.887666”
},
{
“fecha”: “2008-01-11”,
“dato”: “391.975466”
},
{
“fecha”: “2008-01-14”,
“dato”: “392.157918”
},
{
“fecha”: “2008-01-15”,
“dato”: “392.081138”
},
{
“fecha”: “2008-01-16”,
“dato”: “392.033150”
},

Thanks for helping!

ProgFriese · November 21, 2020, 8:07pm

This proxy-output proxytest.txt (135,5 KB) runs into

Sat Nov 21 20:42:39 CET 2020
proxy_test

------
Sat Nov 21 20:42:39 CET 2020
http://localhost:8081/www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64
Unparseable number: "N/E"

java.io.IOException: http://localhost:8081/www.banxico.org.mx/SieAPIRest/service/v1/series/SF61785/datos?locale=en&token=06e4e5817fcc4d7eb2339fe96fd3408ab4abdc10e4077ab65f8bef75c7bd5a64
Unparseable number: "N/E"
	at name.abuchen.portfolio.online.impl.GenericJSONQuoteFeed.parse(GenericJSONQuoteFeed.java:197)
	at name.abuchen.portfolio.online.impl.GenericJSONQuoteFeed.getHistoricalQuotes(GenericJSONQuoteFeed.java:130)
	at name.abuchen.portfolio.online.impl.GenericJSONQuoteFeed.getHistoricalQuotes(GenericJSONQuoteFeed.java:64)
	at name.abuchen.portfolio.ui.jobs.UpdateQuotesJob$2.run(UpdateQuotesJob.java:269)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.text.ParseException: Unparseable number: "N/E"
	at java.base/java.text.NumberFormat.parse(NumberFormat.java:431)
	at name.abuchen.portfolio.online.impl.YahooHelper.asPrice(YahooHelper.java:31)
	at name.abuchen.portfolio.online.impl.GenericJSONQuoteFeed.extractValue(GenericJSONQuoteFeed.java:207)
	at name.abuchen.portfolio.online.impl.GenericJSONQuoteFeed.parse(GenericJSONQuoteFeed.java:182)
	... 4 more

------

jonathlan · November 21, 2020, 9:34pm

Thanks ProgFriese,

Indeed Banxico was sending wrong data:

After changing that to 0.0 PP finally got the information:

Many things learnt here, hope I don’t need to bother you again with this.
Thank you very much!

ProgFriese · November 21, 2020, 10:05pm

Your question was more interesting than the most “my performance seems to be wrong”-questions

jonathlan · November 21, 2020, 11:54pm

That’s pretty cool.
Thank you!

inv-trad · November 22, 2020, 9:33am

When parsing the JSON data in class YahooHelper, values N/A or null would be filtered, but N/E not. So, replacing N/E with N/A should work as well.

jonathlan · November 27, 2020, 10:29pm

Thank you all,

This seems to be working now so I have just published this tool to: https://proxyformatter.herokuapp.com/

To use it, you just need to pass the destination URL after „/format/“, for example:
https://proxyformatter.herokuapp.com/format/https://sampleapis.com/countries/

Of course this only accomplishes my personal issues and almost non error handling, but can be improved if needed.

Regards!