Hello!
I was wondering if it was possible in PP to retrieve the data in a format like this:
…
<script>
window.chart_data = [[858902400000,1000],[859161600000,1003.95],[859248000000,1010.01],[859334400000,1015.92],[859420800000,999.63],[859507200000,999.3],[859766400000,985.36],[859852800000,981.58],[859939200000,974.45]...]]
…
this comes from STOXX® World AC All Cap - Qontigo and the date is in iso format
That would help me a lot!
Thanks!
No, PP isn’t able to parse such dynamic data source.
hey Wick, unfortunately I don’t think PP can handle extracting that without the JSON data being on it’s own URL.
CSV appears to be available on the Stoxx website, which you can at least download & import - but I don’t think you can use the ‘live’ quotes with CSV at the moment.
Others who know better might be able to chime in here!
STOXX® World AC All Cap - Qontigo
→
stoxx.com/document/Indices/Current/HistoricalData/h_3mstxwapgb.txt
Would it be the problem with json for example:
Solactive | Indices with data like this
but not accessible directly by url https://www.solactive.com/Indices/?indexhistory=DE000SLA4GE4&indexhistorytype=max?
Tough, the content is not visible via CURL
curl --compressed -H "User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0" -H "Accept: application/json, text/javascript, */*; q=0.01" -H "Accept-Language: de-DE,en-US;q=0.7,en;q=0.3" -H "Accept-Encoding: gzip, deflate, br" -H "Referer: https://www.solactive.com/Indices/?index=DE000SLA4GE4" -H "X-Requested-With: XMLHttpRequest" -H "DNT: 1" -H "Connection: keep-alive" -H "Sec-Fetch-Dest: empty" -H "Sec-Fetch-Mode: cors" -H "Sec-Fetch-Site: same-origin" -H "TE: trailers" "https://www.solactive.com/Indices/?indexhistory=DE000SLA4GE4&indexhistorytype=max"
Thanks for that!
Is there any way I could use that in Portfolio performance?
@ProgFriese
Danke, die Parameter kannte ich noch nicht. Es müsste mal ausprobiert werden, ob PP beim JSON feed eine response ausgeben kann 
You can use it in a little local proxyserver.
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
geturl="curl --compressed -H \"User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0\" -H \"Accept: application/json, text/javascript, */*; q=0.01\" -H \"Accept-Language: de-DE,en-US;q=0.7,en;q=0.3\" -H \"Accept-Encoding: gzip, deflate, br\" -H \"Referer: https://www.solactive.com/Indices/?index=DE000SLA4GE4\" -H \"X-Requested-With: XMLHttpRequest\" -H \"DNT: 1\" -H \"Connection: keep-alive\" -H \"Sec-Fetch-Dest: empty\" -H \"Sec-Fetch-Mode: cors\" -H \"Sec-Fetch-Site: same-origin\" -H \"TE: trailers\" \"https://www.solactive.com/Indices/?indexhistory=DE000SLA4GE4&indexhistorytype=max\""
geturl | getline 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) + length(ORS)
print "Content-length:", len |& HttpService
print ORS body |& HttpService
# ignore all the header lines
while ((HttpService |& getline) > 0)
continue
# stop talking to this client
close(HttpService)
}
}
start the proxy with
gawk -f proxy.awk
and the proxy is listening on port 8081
:~$ netstat -tanp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
...
tcp 0 0 0.0.0.0:8081 0.0.0.0:* LISTEN 15246/gawk
...
Now you can use it in PP
If you cannot use gawk try to use python or ruby or golang or …
proxy2.go:
package main
import (
// "io/ioutil"
"log"
"net/http"
"fmt"
"os/exec"
// "regexp"
)
func gethistory(w http.ResponseWriter, req *http.Request) {
geturl := "curl --compressed -H \"User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0\" -H \"Accept: application/json, text/javascript, */*; q=0.01\" -H \"Accept-Language: de-DE,en-US;q=0.7,en;q=0.3\" -H \"Accept-Encoding: gzip, deflate, br\" -H \"Referer: https://www.solactive.com/Indices/?index=DE000SLA4GE4\" -H \"X-Requested-With: XMLHttpRequest\" -H \"DNT: 1\" -H \"Connection: keep-alive\" -H \"Sec-Fetch-Dest: empty\" -H \"Sec-Fetch-Mode: cors\" -H \"Sec-Fetch-Site: same-origin\" -H \"TE: trailers\" \"https://www.solactive.com/Indices/?indexhistory=DE000SLA4GE4&indexhistorytype=max\""
body, err := exec.Command("bash", "-c", geturl).Output()
if err != nil {
log.Fatalln(err)
}
jsonstring := string(body)
// fmt.Println(jsonstring) //Test
fmt.Fprintf(w, jsonstring)
}
func headers(w http.ResponseWriter, req *http.Request) {
for name, headers := range req.Header {
for _, h := range headers {
fmt.Fprintf(w, "%v: %v\n", name, h)
}
}
}
func main() {
http.HandleFunc("/gethistory", gethistory)
http.HandleFunc("/headers", headers)
http.ListenAndServe(":8081", nil)
}
start with
go run proxy2.go
Falls Du das meintest was ich darunter verstanden habe, dann ja.
Thanks for the incredible work!
But I must relaunch the program every time I want to retrieve the data am I right?
No, you can start the proxy as a service in the autostart of the host, or you start it when launching PP.
Wow, I’ll put a look on that, however it won’t work on the mobile app that way I guess?
I don’t know anything about the mobile app, only that it supposedly exists.
Haha ok thanks a lot for the help with the desktop app I’ll take a look.
Just a last question, if for examble the data are in a http with url containing the date of the day, for example http://data.com/from=20210918&to=20231121
Is there any way I can automate the todays date in PP?
404
better example:
https://tools.morningstar.co.uk/api/rest.svc/timeseries_price/t92wz0sj7c?currencyId=GBP&idtype=Morningstar&frequency=daily&outputType=JSON&startDate=2020-12-31&id=F00000ZB0M
startDate=2020-12-31
You can automate the date in PP with the macro functions (Kursdaten laden - Portfolio Performance Handbuch), like this
https://tools.morningstar.co.uk/api/rest.svc/timeseries_price/t92wz0sj7c?currencyId=GBP&idtype=Morningstar&frequency=daily&outputType=JSON&startDate={TODAY:yyyy-MM-dd:-P2M}&id=F00000ZB0M
startDate={TODAY:yyyy-MM-dd:-P2M}
-P2M = Today - 2 Month
http://data.com/from={TODAY:yyyy-MM-dd:-P2Y}&to={TODAY}
If you are looking into the requests and responses from such a website, mostly you will find something with json.
You have to look for the headers (and in case of POST-request for the data), in this quontigo-example it looks like
POST /wp-admin/admin-ajax.php HTTP/2
Host: qontigo.com
User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0
Accept: */*
Accept-Language: de-DE,en-US;q=0.7,en;q=0.3
Accept-Encoding: gzip, deflate, br
Referer: https://qontigo.com/index/stxwagr/
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
X-Requested-With: XMLHttpRequest
Content-Length: 60
Origin: https://qontigo.com
DNT: 1
Connection: keep-alive
Cookie: OptanonConsent=isIABGlobal.....blablabla.....&consentId=xxxxxxxxxxxxx.....
Sec-Fetch-Dest: empty
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
TE: trailers
action=index_detail_refresh&index_id=51174&isin=CH0462362101
In order to be able to load the data reproducibly, a suitable cookie must always be used in this case.
To do this, you have to use curl twice, once to get a fresh cookie, and the second time to load the desired data with this cookie.
curl --cookie-jar quontigo.txt "https://qontigo.com/index/stxwagr/"
–cookie-jar saves the received cookie in the file quontigo.txt
curl --compressed --cookie quontigo.txt -H "User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0" -H "Accept: */*" -H "Accept-Language: de-DE,en-US;q=0.7,en;q=0.3" -H "Accept-Encoding: gzip, deflate, br" -H "Referer: https://qontigo.com/index/stxwagr/" -H "Content-Type: application/x-www-form-urlencoded; charset=UTF-8" -H "X-Requested-With: XMLHttpRequest" -H "Content-Length: 60" -H "Origin: https://qontigo.com" -H "DNT: 1" -H "Connection: keep-alive" -H "Sec-Fetch-Dest: empty" -H "Sec-Fetch-Mode: cors" -H "Sec-Fetch-Site: same-origin" -H "TE: trailers" -d "action=index_detail_refresh" -d "index_id=51174" -d "isin=CH0462362101" "https://qontigo.com/wp-admin/admin-ajax.php"
–cookie uses the data from quontigo.txt
quontigo.go:
package main
import (
"log"
"net/http"
"fmt"
"os/exec"
)
func gethistory(w http.ResponseWriter, req *http.Request) {
getcookie := "curl --cookie-jar quontigo.txt \"https://qontigo.com/index/stxwagr/\""
body, err := exec.Command("bash", "-c", getcookie).Output()
if err != nil {
log.Fatalln(err)
}
getjson := "curl --compressed --cookie quontigo.txt -H \"User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:120.0) Gecko/20100101 Firefox/120.0\" -H \"Accept: */*\" -H \"Accept-Language: de-DE,en-US;q=0.7,en;q=0.3\" -H \"Accept-Encoding: gzip, deflate, br\" -H \"Referer: https://qontigo.com/index/stxwagr/\" -H \"Content-Type: application/x-www-form-urlencoded; charset=UTF-8\" -H \"X-Requested-With: XMLHttpRequest\" -H \"Content-Length: 60\" -H \"Origin: https://qontigo.com\" -H \"DNT: 1\" -H \"Connection: keep-alive\" -H \"Sec-Fetch-Dest: empty\" -H \"Sec-Fetch-Mode: cors\" -H \"Sec-Fetch-Site: same-origin\" -H \"TE: trailers\" -d \"action=index_detail_refresh\" -d \"index_id=51174\" -d \"isin=CH0462362101\" \"https://qontigo.com/wp-admin/admin-ajax.php\""
body, err = exec.Command("bash", "-c", getjson).Output()
if err != nil {
log.Fatalln(err)
}
jsonstring := string(body)
fmt.Fprintf(w, jsonstring)
}
func headers(w http.ResponseWriter, req *http.Request) {
for name, headers := range req.Header {
for _, h := range headers {
fmt.Fprintf(w, "%v: %v\n", name, h)
}
}
}
func main() {
http.HandleFunc("/", gethistory)
http.HandleFunc("/headers", headers)
http.ListenAndServe(":8083", nil)
}
So, you have to use
$.data.chart_data[*][0]
$.data.chart_data[*][1]
You can do this to have the data complete once, but because this method always loads all data (since 1997), it is quite slow and actually stupid.
It would be better (for quontigo and for you) to use the link from @Abacus6923. This is a csv, but you can change that.
quontigo2.go:
package main
import (
"io/ioutil"
"encoding/json"
"strings"
"log"
"net/http"
"fmt"
)
var nlines int = 0
type Record struct {
Date string `json:"date"`
Index string `json:"index"`
Value string `json:"value"`
}
func h_3m_from_stoxx(w http.ResponseWriter, req *http.Request) {
geturl := "https://www.stoxx.com/document/Indices/Current/HistoricalData" + req.URL.Path + ".txt"
resp, err := http.Get(geturl)
if err != nil {
log.Fatalln(err)
}
body, err := ioutil.ReadAll(resp.Body)
if err != nil {
log.Fatalln(err)
}
jsonstring := string(body)
records := []Record{}
lines := strings.Split(jsonstring, "\n")
for key, _ := range lines[0:] {
nlines = key
}
for _, line := range lines[1:nlines] {
fields := strings.Split(line, ";")
date := fields[0]
index := fields[1]
value := fields[2]
records = append(records, Record{
Date: date,
Index: index,
Value: value,
})
}
jsonData, err := json.Marshal(records)
if err != nil {
panic(err)
}
fmt.Fprintf(w, string(jsonData))
}
func headers(w http.ResponseWriter, req *http.Request) {
for name, headers := range req.Header {
for _, h := range headers {
fmt.Fprintf(w, "%v: %v\n", name, h)
}
}
}
func main() {
http.HandleFunc("/", h_3m_from_stoxx)
http.HandleFunc("/headers", headers)
http.ListenAndServe(":8084", nil)
}
$[*].date
$[*].value
HTH