staffordian wrote:Jam1 wrote:
Also failed for me tonight.
Same here ..
I've knocked up a '
rough and ready' VBA routine that will use the still-working and recently more reliable
v10 API from Yahoo and scrape prices rapidly for the Excel version of HYPTUSS.
For now, I've inserted an '
Alt Yahoo' button underneath the '
Portfolio Value' top-left area, using the steps shown in the video below from 55 seconds in, until the 3 minute mark -
https://player.vimeo.com/video/707670466Here's the '
Alt Yahoo' VBA code to use with the above process -
Code: Select all
Private Sub CommandButton17_Click()
Dim lastrow, rowsdown, epic, url_string, http, sResp, price, regexp
Let lastrow = Range("C65536").End(xlUp).Row
' Clear existing price cells so that the user can see the new prices being populated
For rowsdown = 6 To lastrow
Cells(rowsdown, 5) = ""
Next rowsdown
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Set regexp = CreateObject("VBScript.RegExp")
For rowsdown = 6 To lastrow
'Set the Epic for the row
epic = Cells(rowsdown, 3)
' Set the Yahoo Finance v10 API URL for the EPIC in each row
Let url_string = "https://query2.finance.yahoo.com/v10/finance/quoteSummary/" & epic & ".l?modules=price"
'Get the Yahoo Finance JSON for the EPIC
With http
.Open "GET", url_string, False
.send
sResp = .responseText
End With
On Error Resume Next
'Find the Yahoo Finance Yield in the returned JSON if it exists
With regexp
.Pattern = "regularMarketPrice[\s\S]+?fmt"":""(.*?)"""
If .Execute(sResp).Count > 0 Then
price = .Execute(sResp)(0).SubMatches(0)
End If
End With
' Place the v10 API Yahoo share price on the HYP sheet
Cells(rowsdown, 5) = price
Next rowsdown
End SubThe above process works for the latest version of the HYPTUSS Excel download, and the only check required for anyone wanting to give it a go
with an older version is to make a note of the '
CommandButton' number that exists before deleting the two original lines of button code at the 2-minute mark in the above video, and then after pasting in the above VBA code into the VBA Editor, make sure that the first
CommandButton number
is manually realigned to that same original number. In the above video, we can see that the original
CommandButton number is
1, and in the latest version of the HYPTUSS Excel download, it's '
17' as replicated in the above VBA code, but it is likely to be a
different number in various older versions of the HYPTUSS tool, so just check before and after the copy and paste process that the number is the same as the first line of VBA button-code originally deleted...
The VBA password box is likely to appear at some stage early in the above process, and the open VBA password for HYPTUSS is the usual '
pleaseletmein'
As usual, the above steps are really quite simple for anyone with enough care and patience to follow the steps shown in the video, and I'd welcome any feedback if anyone gives it a go, but please,
at first make sure that you work on a recent COPY of your current HYPTUSS file, in case anything un-towards happens during the above process...
Cheers,
Itsallaguess
C.