In response to privacy issues encountered by other web service providers, on or about May 13, 2018 Yahoo modified their web page layouts to incorporate click-through privacy policy notices along with a few other additions.
These changes do not dramatically change the appearance of the web pages in a browser. However, these changes did radically alter the landmarks I was using to capture market state and time from the raw HTML for these pages.
This problem exists in the 7 Traders Tips spreadsheets from December 2017 through June 2018.
The following fix requires changes to 2 VBA procedures in each of those 7 workbooks. If you are comfortable with the EXCEL built-in VBA editor then you can make the required changes as follows:
Open the VBA editor. Alt-F11 will do the trick In the VBA editor, open the FIND dialog. CTRL-F will do this. Replace whatever is in the "Find What" box with: Sub DriveFormatPriceHistory In the Search box, click the radio button next to Current Project. Then click the "Find Next" button. Then close the find dialog to get it out of your way. Scroll down within the DriveFormatPriceHistory procedure to find the following 15 lines of code. mNdx = InStrRev(MarketState, ":", -1, vbTextCompare) If IsNumeric(Mid(MarketState, mNdx - 1, 1)) And IsNumeric(Mid(MarketState, mNdx + 1, 1)) Then ' have a time stamp in MarketState mNdx1 = InStrRev(MarketState, " ", mNdx, vbTextCompare) ' prior blank mNdx2 = InStr(mNdx, MarketState, " ", vbTextCompare) ' trailing blank Chunk1 = Trim(Left(MarketState, mNdx1)) Chunk2 = Trim(Mid(MarketState, mNdx1 + 1, mNdx2 - 1 - mNdx1)) Chunk2 = Left(Chunk2, Len(Chunk2) - 2) & " " & Right(Chunk2, 2) ' put a blank in it Chunk3 = Trim(Mid(MarketState, mNdx2)) Else ' have no time stamp Chunk1 = Trim(Left(MarketState, mNdx)) Chunk2 = WorksheetFunction.Text(Now(), "H:MM AM/PM") Chunk3 = Trim(Mid(MarketState, mNdx + 1)) End If Delete those 15 lines and replace them with the following 21 lines of code. Copy and paste of the entire 21 line replacement block can save a bit of typing. mNdx = InStrRev(MarketState, ":", -1, vbTextCompare) If mNdx = 0 Then ' have no full colon so no timestamp Chunk1 = "LocalTime" Chunk2 = WorksheetFunction.Text(Now(), "H:MM AM/PM") Chunk3 = "Found """ & MarketState & """ to be in error!" ElseIf IsNumeric(Mid(MarketState, mNdx - 1, 1)) And IsNumeric(Mid(MarketState, mNdx + 1, 1)) Then ' have a reasonable time stamp in MarketState text string ' parse out the pieces to make pretty mNdx1 = InStrRev(MarketState, " ", mNdx, vbTextCompare) ' prior blank mNdx2 = InStr(mNdx, MarketState, " ", vbTextCompare) ' trailing blank Chunk1 = Trim(Left(MarketState, mNdx1)) Chunk2 = Trim(Mid(MarketState, mNdx1 + 1, mNdx2 - 1 - mNdx1)) Chunk2 = Left(Chunk2, Len(Chunk2) - 2) & " " & Right(Chunk2, 2) ' put a blank in it Chunk3 = Trim(Mid(MarketState, mNdx2)) Else ' have ":" but rest does not look like a time stamp (not in expected format) Chunk1 = "LocalTime" Chunk2 = WorksheetFunction.Text(Now(), "H:MM AM/PM") Chunk3 = "Found """ & MarketState & """ not in expected format!" End If Once again, open the FIND dialog. (CTRL-F) Replace whatever is in the "Find What" box with: Sub ExtractCompanyIdFromSummaryText Verify that the radio button next to Current Project is still selected. Then click the "Find Next" button. Then close the find dialog to get it out of your way. Scroll down within the ExtractCompanyIdFromSummaryText procedure to find the following 20 lines of code. ' forward space to <div id="quote-market-notice" land mark ' This will be found just before the particular instance ' of data-reactid="40" or 41 that we would like to use firstpos = InStr(LastPos, StockSummaryPage_Html_txt, "<div id=""quote-market-notice""", vbTextCompare) ' THEN for the market state and timestamp ' forward space to data-reactid="40"> or if that is a null value then ' forward space to data-reactid="41"> (not sure why it moves between these two) firstpos = InStr(LastPos, StockSummaryPage_Html_txt, "data-reactid=""40"">", vbTextCompare) firstpos = firstpos + 18 ' bump over eyecatcher text to first char of market state text LastPos = InStr(firstpos, StockSummaryPage_Html_txt, "<", vbTextCompare) TextLen = LastPos - firstpos If TextLen = 0 Then ' null string at id = 40 so lets try id = 41 firstpos = InStr(LastPos, StockSummaryPage_Html_txt, "data-reactid=""41"">", vbTextCompare) firstpos = firstpos + 18 ' bump over eyecatcher text to first char of market state text LastPos = InStr(firstpos, StockSummaryPage_Html_txt, "<", vbTextCompare) TextLen = LastPos - firstpos End If TimeAndState = Mid(StockSummaryPage_Html_txt, firstpos, TextLen) Delete those 21 lines and replace them with the following 32 lines of code. Again, copy and paste of the entire 32 line replacement block will work to save a bit of typing. ' Forward space to <div id="quote-market-notice" land mark firstpos = InStr(LastPos, StockSummaryPage_Html_txt, "<div id=""quote-market-notice""", vbTextCompare) ' My eyeball scan of the raw HTML says no inner Divs at this time. ' So we can use a simple search for the end of this div LastPos = InStr(firstpos, StockSummaryPage_Html_txt, "</div>", vbTextCompare) ' Now back space to char just in front of an expected </span> tag ' which should be jambed up tight in front of the </div> we just found. LastPos = LastPos - 8 If Mid(StockSummaryPage_Html_txt, LastPos + 1, 7) <> "</span>" Then ' Not a "</span>" tag at this exact location!! ' Construction is not as we have been expecting! Stop ' to let the world know where we found this problem. ' ' NOTE: At this time, you may use PF5 (function key 5) to resume processing. ' TimeAndState = "Error, end span tag ""</span>"" not found as expected." Exit Sub End If TimeAndState = "" ' now scan backwards to locate the ">" character that ' immediately preceeds the market Status and Time text Dim ndx as long For ndx = LastPos To firstpos Step -1 If Mid(StockSummaryPage_Html_txt, ndx, 1) = ">" Then ' found it! ' grab the text we want TextLen = LastPos - ndx TimeAndState = Mid(StockSummaryPage_Html_txt, ndx + 1, TextLen) ' and then get out of Dodge City Exit For End If Next ndx Close the VBA editor window. Save your freshly edited work book. Test the work book as you see fit