Updates to Excel HYPTUSS, Simple Price Scrape and StepOneFTSE350

[kiloran]

I noticed that the Excel macro which is used to download prices from Yahoo has failed over the past few days.

It looks like the macro is downloading some extraneous but invisible characters in front of the Yahoo share symbol and this is corrupting the operation of the macro. Very strangely, it fails on my Windows 11 laptop with Excel 2019, but is OK on a Windows 10 laptop with Excel 2010.

Anyway, I've updated the macro to strip out the extraneous characters and published the following new versions:

1. HYPTUSS v11.87 at: https://lemonfoolfinancialsoftware.weeb ... op-up.html
   
2. Simple Price scrape v2.3 at: https://lemonfoolfinancialsoftware.weeb ... crape.html
   
3. StepOne FTSE350 v1.44 at: https://lemonfoolfinancialsoftware.weeb ... se350.html

I know I said HYPTUSS would no longer be updated, but it was a simple change to copy the new code.

The LibreOffice versions of these tools are unaffected (famous last words!)

--kiloran

[seagles]

Cheers Kiloran,
If I update HYPTUSS I will need to do some work on my other linked Excel spreadsheet. Not a major problem but will take a bit of time. Any chance you "post" the change so I can just update my current version? Then I can update everything later whenI have the time (and energy)

[kiloran]

Cheers Kiloran,
If I update HYPTUSS I will need to do some work on my other linked Excel spreadsheet. Not a major problem but will take a bit of time. Any chance you "post" the change so I can just update my current version? Then I can update everything later whenI have the time (and energy)

OK, I think these are all the changes....

1. Open the Visual Basic editor for Sheet1 (High Yield Portfolio)
2. Locate the subroutine getYahooData()
3. Replace

   Code: Select all

   symbol = trow.Children.item(symbolCol).innerText
   with

   Code: Select all

   symbol = cleanString(trow.Children.item(symbolCol).innerText)
4. After the end of the getYahooData() subroutine, add the function

   Code: Select all

   ' clean up the string and remove any odd characters
Function cleanString(text As String) As String
    Dim output As String
    Dim c 'since char type does not exist in vba, we have to use variant type.
    Dim i
    For i = 1 To Len(text)
        c = Mid(text, i, 1) 'Select the character at the i position
        If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") _
            Or c = "^" Or c = "." Or c = "-" Or c = "=" Then
            output = output & c 'add the character to your output.
        Else
            output = output & ""
        End If
    Next
    cleanString = output
End Function
5. Repeat steps 1-4 for sheet4 (Watchlist)

Hope that helps

--kiloran

[seagles]

Sod's law. My microsoft office suite received an updated and now no longer works. So in process of "repairing" it. Once that it done will do the update. I may have the time today to actually do the proper thing and update my spreadsheets. Depends on how long it takes to work out what is wrong, currently letting microsoft "repair" do its thing.

[kiloran]

Sod's law. My microsoft office suite received an updated and now no longer works. So in process of "repairing" it. Once that it done will do the update. I may have the time today to actually do the proper thing and update my spreadsheets. Depends on how long it takes to work out what is wrong, currently letting microsoft "repair" do its thing.

That's a frustration, since it's almost certain that it was a Microsoft update to the OS or Office that caused the problem in the first place.

--kiloran

[seagles]

Now that was strange. I could not find the getYahooData(). Checked your code and realised that I had a "chunk" missing. Then found I was still at Version 11.85? However, everything, for me, has been running ok. So run the "get prices" and everything worked, so it seems the "new" Yahoo price macro may be the reason. Anyway will, at some point update to 11.87. Only thing I seem to be missing (for what I use) is the FTSE-HYP Tracking data for the FTSE, which I have not used for any meaningful purpose previously. I wonder why I never upgraded too 11.86? I must have tried it and tested it, as I always liked to test your upgrades, but for some reason never got around to updating my own HYPTUSS (or the other ones I have used).

[seagles]

I know HYPTUSS is no longer being maintained but just to let those that upgrade to 11.87 that I have found during my upgrade of a small protfolio that everything seems to work fine (well all that i have used and tested that is) apart form the Tracking TAB. It no longer updates the Benchmark 1 and 2 fields and also does not add additional lines for each press of "Get prices" and "Get yields", it seems to override the last updated line (could be date dependant, willl need to check tomorrow to see if it adds a "new line").

[seagles]

I know HYPTUSS is no longer being maintained but just to let those that upgrade to 11.87 that I have found during my upgrade of a small protfolio that everything seems to work fine (well all that i have used and tested that is) apart form the Tracking TAB. It no longer updates the Benchmark 1 and 2 fields and also does not add additional lines for each press of "Get prices" and "Get yields", it seems to override the last updated line (could be date dependant, willl need to check tomorrow to see if it adds a "new line").

So using "get yields" or "get prices" is currently overiding the last entry in the tracking tab. My workaround is to add figures to the first 2 columns, then it will add a "new" line next time you use either of the two "buttons".
Have "upgraded" my major spreadsheet with all my changes and everything works as previously (other than I spotted an error with one of my formula's, no idea how long that has been wrong though, fortunately I have not purchased any shares based on the figure that was wrong).
Thanks again Kiloran for all your work on this. I am now considering what to do going forward as I switched to HYPTUSS completely after you added quite a few of my "requests". Maybe I will just leave it as I no longer do much with my portfolio and what I do, currently, only affects my ITs in my SIPP and I can manage that with another spreadsheet I have. I can also manage my daughters ISA and granddaughters JISA if HYPTUSS does go "belly up" in the future, with little effort in creating a new spreadsheet (small amount of holdings and once a year addition).

[kiloran]

I know HYPTUSS is no longer being maintained but just to let those that upgrade to 11.87 that I have found during my upgrade of a small protfolio that everything seems to work fine (well all that i have used and tested that is) apart form the Tracking TAB. It no longer updates the Benchmark 1 and 2 fields and also does not add additional lines for each press of "Get prices" and "Get yields", it seems to override the last updated line (could be date dependant, willl need to check tomorrow to see if it adds a "new line").

So using "get yields" or "get prices" is currently overiding the last entry in the tracking tab. My workaround is to add figures to the first 2 columns, then it will add a "new" line next time you use either of the two "buttons".
Have "upgraded" my major spreadsheet with all my changes and everything works as previously (other than I spotted an error with one of my formula's, no idea how long that has been wrong though, fortunately I have not purchased any shares based on the figure that was wrong).
Thanks again Kiloran for all your work on this. I am now considering what to do going forward as I switched to HYPTUSS completely after you added quite a few of my "requests". Maybe I will just leave it as I no longer do much with my portfolio and what I do, currently, only affects my ITs in my SIPP and I can manage that with another spreadsheet I have. I can also manage my daughters ISA and granddaughters JISA if HYPTUSS does go "belly up" in the future, with little effort in creating a new spreadsheet (small amount of holdings and once a year addition).

Apologies, I forgot about the Tracking Sheet when I made the changes. The problem was caused by Excel/VBA adding extraneous characters when retrieving the FTSE indices from Yahoo, the same problem we saw with price retrieval.

The fix is very simple. At the end of the code for the "High Yield Portfolio" sheet, in the function "getindex", change

Code: Select all

If hrow.Cells(0).innerText = index Then
        'Debug.Print hrow.Cells(0).innerText, hrow.Cells(1).innerText
         indexValue = hrow.Cells(1).innerText
 End If


to

Code: Select all

If cleanString(hrow.Cells(0).innerText) = index Then
      'Debug.Print hrow.Cells(0).innerText, hrow.Cells(1).innerText
      indexValue = cleanString(hrow.Cells(1).innerText)
End If

I updated HYPTUSS to v11.88. Although we said HYPTUSS would no longer be maintained, it would seem a little churlish not to implement such a simple fix.

--kiloran

[seagles]

Changed code and works well. Thanks for the update.