Page 1 of 1

LivePrice code

Posted: November 14th, 2017, 6:37 pm
by RaspberryFool
I'll start a new post rather than muddy the water of the HYPTUS v11.31 Run-time error 5 thread...

Many years ago I managed to teach myself VBA and successfully cribbed anc1's Liveprice code to download the prices of my portfolio into an Access database rather than an Excel spreadsheet. (Thank you anc1/eventide by the way). This worked fine for many years but has, obviously, now hit the same problems as the HYPTUS spreadsheet code.

My problem is that a change of job and two young kids means that I have not looked at VBA code since around that time and old age is now having an impact on my understanding of it! JSON dictionaries are completely new to me, as is Python.
The post from PeterGray looked as if it might offer the start of a solution, but I was concerned with eventide's comment of "Although this may work as intended, isn't it a serious amount of inefficient code overhead to extract a single field from the server response? Why would anyone choose to pull data like this?"

So is there a simple solution for someone with very rusty VBA knowledge to bring back the required data (as a JSON dictionary?) and be able to convert it to a VBA array so that the rest of my code can work as before?

Cheers
RF

Re: LivePrice code

Posted: November 14th, 2017, 7:00 pm
by johnhemming
I don't know that much about the details. I used to use Yahoo's price service to update Open Office, but am just monitoring prices at the moment and using my broking accounts. JavaScript Object Notation (JSON) is a bit odd to people used to things like CSV files or indeed arrays. That is because it has potentially objects within objects which can get very complicated. It is much like XML in many ways, but much easier to read. There is a useful online parser here:
http://json.parser.online.fr/

JSON is a good encoding to use if you are writing in Javascript. Because JSON is generally used for REST (Representational State Transfer) servers in passing data around you may end up being forced into changing to use it. I use it a lot for the work I am doing. I would think there would be standard functions in any more recent software that convert objects into JSON strings and parse the strings into objects. To really make it work you need, however, to find good functions that give access to parts of the resultant objects.

This may not help, but I think in the long term you will have to live with JSON so you might as well make the change now.

Re: LivePrice code

Posted: November 14th, 2017, 7:53 pm
by desmid
RaspberryFool wrote:So is there a simple solution for someone with very rusty VBA knowledge to bring back the required data (as a JSON dictionary?) and be able to convert it to a VBA array so that the rest of my code can work as before?

Well, I know this isn't quite the helpful answer you seek, but a quick search with "VBA json" turned up a few things and you could grab one of those. For example and taking the first hit: https://github.com/VBA-tools/VBA-JSON

For the record, python has the 'json' module as part of its standard library, though I've not used it yet.

cheers
desmid

Re: LivePrice code

Posted: November 14th, 2017, 10:27 pm
by eventide
Handling json in vba : pia
handling json in python: breeze

Rusty vba skills: why bother. just download the addin and use the simple getdata function at this link

viewtopic.php?p=94736#p94736

Re: LivePrice code

Posted: November 15th, 2017, 10:22 am
by RaspberryFool
eventide wrote:Handling json in vba : pia
handling json in python: breeze

Rusty vba skills: why bother. just download the addin and use the simple getdata function at this link

viewtopic.php?p=94736#p94736


... because, if I'm not mistaken, the getdata function is for use in a spreadsheet. I'd like something I can use in an Access database.

Re: LivePrice code

Posted: November 15th, 2017, 10:50 am
by johnhemming
JSON can handle arrays, but does not only do so and you might need to go into the JSON a bit to find the array. What follows below is a "pretty print" of some JSON that I am using to control communications between a browser and the phone system. The curly brackets indicate the start of an object { or end of an object }. The square brackets indicate the start or end of an Array of objects with a comma that separates each object.

Hence if you look at below you will see the object has first a property called "channel-bundles" which has an array of channel bundles. Each (channel bundle) has an Id, and a transport (The transport has an array of candidates) the transport (not the channel bundle, note the indent) has also got "ufrag": "7k8M", "rtcp-mux": true, "pwd": sza6TPlmaafxwWeZKtvz8Ce", and "fingerprints": fingerprints then has an array within it. Hence there are ways Json can use arrays, but it may be somewhere in the object rather than at the top.

Always try to get a way of "pretty printing" json as it is much easier to read. The website I gave a link to earlier will take a string and "pretty print" it.
{
"channel-bundles": [
{
"id": "59be29d7",
"transport": {
"candidates": [
{
"generation": 0,
"component": 1,
"protocol": "udp",
"port": 57173,
"ip": "2001::4137:9e76:c35:355d:ae75:cf96",
"foundation": "267343796",
"priority": 2122255103,
"type": "host"
},
{
"generation": 0,
"component": 1,
"protocol": "udp",
"port": 57174,
"ip": "81.138.48.105",
"foundation": "4226102127",
"priority": 2122194687,
"type": "host"
}
],
"xmlns": "urn:xmpp:jingle:transports:ice-udp:1",
"ufrag": "7k8M",
"rtcp-mux": true,
"pwd": "3sza6TPlmaafxwWeZKtvz8Ce",
"fingerprints": [
{
"fingerprint": "30:26:12:94:EA:99:3C:62:6F:7E:B2:57:98:EB:4C:9E:81:30:FD:DD:BB:7D:87:FE:A6:31:71:3B:A5:2A:DE:A8",
"setup": "actpass",
"hash": "sha-256"
}
]
}
}
],
"contents": [
{
"channels": [
{
"endpoint": "59be29d7",
"payload-types": [
{
"name": "PCMA",
"id": 8,
"clockrate": 8000
},
{
"name": "telephone-event",
"id": 101,
"clockrate": 8000
}
],
"channel-bundle-id": "59be29d7",
"sources": [
200200200
],
"rtp-level-relay-type": "mixer",
"expire": 60,
"initiator": true,
"id": "f635958e01e30152",
"direction": "sendrecv"
}
],
"name": "audio"
},
{
"channels": [
{
"endpoint": "59be29d7",
"payload-types": [
{
"name": "VP8",
"id": 100,
"clockrate": 90000
},
{
"name": "red",
"id": 116,
"clockrate": 90000
},
{
"name": "ulpfec",
"id": 117,
"clockrate": 90000
},
{
"name": "H264",
"id": 127,
"clockrate": 90000
}
],
"channel-bundle-id": "59be29d7",
"sources": [
1268941499
],
"rtp-level-relay-type": "translator",
"expire": 60,
"initiator": true,
"id": "1973f9a6a1fb83cc",
"direction": "sendrecv",
"last-n": -1
}
],
"name": "video"
},
{
"sctpconnections": [
{
"endpoint": "59be29d7",
"channel-bundle-id": "59be29d7",
"port": 5000,
"expire": 60,
"initiator": true,
"id": "ee0ebfaaa5712867"
}
],
"name": "data"
}
],
"id": "4c68e6242021ce55"
}

Re: LivePrice code

Posted: November 15th, 2017, 10:58 am
by eventide
RaspberryFool wrote:
... because, if I'm not mistaken, the getdata function is for use in a spreadsheet. I'd like something I can use in an Access database.





Ah yes, missed that sorry.

With the html responseText from

https://query1.finance.yahoo.com/v7/fin ... D.L,HSBA.L, ... , BP.L (concatenate whatever your tickers are)

I'm certain that you can manipulate the string in Access VB by splitting it at "{" and packing an array or dictionary, even if it is a bit messy. A dictionary is better now because the server response is not usually in the same ticker order as the query for some reason.


Edit: Or it may be possible to instantiate excel within access and reference the addin getdata functions directly

Re: LivePrice code

Posted: November 15th, 2017, 12:15 pm
by RaspberryFool
eventide wrote:
RaspberryFool wrote:
... because, if I'm not mistaken, the getdata function is for use in a spreadsheet. I'd like something I can use in an Access database.





Ah yes, missed that sorry.

With the html responseText from

https://query1.finance.yahoo.com/v7/fin ... D.L,HSBA.L, ... , BP.L (concatenate whatever your tickers are)

I'm certain that you can manipulate the string in Access VB by splitting it at "{" and packing an array or dictionary, even if it is a bit messy. A dictionary is better now because the server response is not usually in the same ticker order as the query for some reason.


Edit: Or it may be possible to instantiate excel within access and reference the addin getdata functions directly


eventide,
thanks for the suggestion. I was coming to the conclusion that it would be a case of changing the existing code to manipulate the returned JSON string. In my case, I load the unstrung data into an Access table - so I doubt that trying to create a dictionary would be needed.

I just now need to find the time and a duster for my memory cells ...

RF

Re: LivePrice code

Posted: November 17th, 2017, 1:54 pm
by Itsallaguess
RaspberryFool wrote:
So is there a simple solution for someone with very rusty VBA knowledge to bring back the required data (as a JSON dictionary?) and be able to convert it to a VBA array so that the rest of my code can work as before?


If you're put off trying to wade through the more complicated VBA code in the HYPTUSS tool for inspiration, then Kiloran and I have just updated the 'Simply Yahoo Price Scrape' tool, which more or less does exactly what you're after above, and should be much simpler to follow as it's only a couple of pages of code.

I think I may have even put some helpful code-comments in there as well, if I remember rightly! :O)

viewtopic.php?f=27&t=8449

http://lemonfoolfinancialsoftware.weebl ... crape.html

With regards to any discussion regarding code-efficiency and the like, then I'm very much of the mind that functionality always comes above process, so please don't let that put you off giving this a go. I will certainly never win anything myself at the 'Proficient-Coding' award-nights....

If you've got any questions regarding the code in the Simple-Price-Scrape VBA, then please just ask and I'll try to help out. Be aware that whilst we put a basic password on the HYPTUSS VBA (pleaseletmein for anyone interested - it's for user-protection rather than lock-out purposes...), we don't protect the Simple VBA code at all, so you should be able to go right in and take a peek.

The main sheet in the Simple tool can also be used as a front-end for anyone wanting to take a look at building there own price-scrape / portfolio management spreadsheet, so might be worth a look for those that have not seen it before.

Cheers,

Itsallaguess

Re: LivePrice code

Posted: November 17th, 2017, 3:22 pm
by RaspberryFool
Thanks Itsallaguess and everyone else for your helpful replies. Despite the rustiness, I have managed to update my VBA code and it appears to work :-) I have also incorporated the strComp function for my 'GBP' vs 'GBp' comparison!
When I realised that it was simply a matter of interpreting the returned string of data differently rather than concerning myself with JSON, dictionaries, Python etc. the task became more straightforward.
I just hope that Yahoo doesn't change their site again in the near future!

BTW, are there any sites that provide similar price APIs for managed funds?

RF

Re: LivePrice code

Posted: November 17th, 2017, 3:28 pm
by eventide
RaspberryFool wrote:
BTW, are there any sites that provide similar price APIs for managed funds?

RF


The next time I update the addin it will have a function to collect a dozen or so fields for managed funds if you have the ISIN. It wont be nearly as fast the function for collecting stock prices which can handle 2000 in a fraction of a second.


Also the yahoo server already returns prices for *some* funds if you append ".L" or ".IE" or ".LU" to the ISIN, dependent on the jurisdiction of the assets