VB Script to Fetch "Effective Date" from Website?

[Rick-O-Shay]

Hi,

I am trying to make a simple script that when run will populate cell A1 with the text: "Rates shown are effective xx/xx/xx" which is displayed on this website: https://www.wsj.com/market-data/bonds

Is there a way to pull just this text in? I have a script that pulls the rates table in, which works great, but I need a way to import the effective date to make sure the data from the website if correct.

[Rick-O-Shay]

I guess I should ask, should it be incorporated into my script for the rate data that is pulled? If so, Here is my script:

Please Login or Register  to view this content.

[Rick-O-Shay]

Is there a way to gather the info from the site source code maybe?

[AliGW]

Which date are you trying to pull? I can see both 11 and 12 July as headers at various points on the page - which one do you need?

[Rick-O-Shay]

I need the 11th, it should be the one a day behind, since the rates are for the previous day.

[AliGW]

OK, but please clarify where on the page you wish to take it from. There are several dates - you need to be specific (which table?).

[Rick-O-Shay]

My apologies, I think it's table 1. I'm importing the rates from table 1 (Libor Rates) and want that corrosponding effective date included with my data.

[AliGW]

Thanks - hopefully one of our VBA experts will be able to tell you how to grab that from the web page.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[Marc L]

Is there a way to gather the info from the site source code maybe?

A starter demonstration :

Sub Demo() 
        Dim L& 
    With CreateObject("WinHttp.WinHttpRequest.5.1") 
        .Open "GET", "https://www.wsj.com/market-data/bonds", False 
        .setRequestHeader "DNT", "1" 
        .send 
     If .Status = 200 Then 
         L = InStr(.responseText, "Rates shown are effective") 
         If L Then MsgBox Mid(.responseText, L, InStr(L, .responseText, "</") - L) 
     End If 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[YasserKhalil]

Another little variation of the great code of Marc L

Please Login or Register  to view this content.

[Rick-O-Shay]

Marc L,

That little bit did it. But is there a way to make a slight modification? How can I incorporate it into my existing script so that it works at the same time? Also, instead of a messagebox, I want the result to display in a specific cell, such as A12 in my current situation.

Sorry, I'm still learning vb scripting.

Edit**

I think I was able to just incorporate the code into my existing script, now I just need to display results in a cell.

[YasserKhalil]

Just replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[Rick-O-Shay]

Thank you both! This made my day!!

[Rick-O-Shay]

One last question on this:

Everything worked when I updated my script while I was at work. We use Excel 2010 at my work. I then emailed the file to myself and tried to open the exact same file at home and when I run the script it errors. I use Excel 2016. I don't understand why a version change would break the script.

Error Code: System Error &H80072F7D (-2147012739). An error occurred in the secure channel support.

[Marc L]

If my code I have no idea as it well works on my side …

[Rick-O-Shay]

Here is how I have the code embedded. It works find on Excel 2010 but errors on 2016:

Please Login or Register  to view this content.