Data acquisition from the web

[bknight]

At least for one time no issues.
Thanks again.

[bknight]

I don't wish to beat a dead horse, but...….
For the last several days I have been unsuccessful in running the my macro, encountering the same problem "can't dl the object because your browser..."
Well for a lot of mornings I get this error message when opening Excel. So Excel remembers that I wanted to open it.
Just for your information.

[HansV]

I hope that Rudi or someone else has a suggestion for you...

[bknight]

Me too. I for got to add that the code worked this morning, but only after many attempts, just like yesterday morning. It did fail in the evening though, its almost like their data table(not the web form is incomplete and completes sometime after they get back to work?

[Rudi]

Can you determine if the error originates fro the query or from the VBA code?

If from the query, try to click on each step and determine which step is failing? If that step contains the path listed in the error, ensure it is still correct or that this path opens to a valid page on the web.

[bknight]

Can you determine if the error originates fro the query or from the VBA code?

If from the query, try to click on each step and determine which step is failing? If that step contains the path listed in the error, ensure it is still correct or that this path opens to a valid page on the web.

Um I have stepped through the code and it stops at the

Code: Select all

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.cboe.com/publish/scheduledtask/mktdata/datahouse/vixcurrent.csv", Destination:=Range("$A$1"))

And when he code works nothing has changes coding wise it just executes. I did something different tonight to see if it would work and the code functioned.
Normally I put my lap into sleep/hibernate I'm not sure where to check what happens when I shut the lid, but Excel is running with the particular workbook open.
Tonight I saved and shut down Excel before I closed the lid. When I opened the lid, I restarted two workbooks that both contain codes to access data on the web. One I never have any issues and the problematic one, but it ran fine this evening. I'll post a continuing saga.

[Rudi]

I wouldn't be sure, but maybe the code refreshed while the 'csv' document was not available on the site? It's hard to say for sure??? Bottom line is if the code used to work (or works periodically), then the issue is not with the code but with the external environment, like the website or with connection, etc... (in this case, it is outside our means to support!)

[bknight]

I fully agree with that thought and that is why I'm still looking for suggestions. In the recent past when this error occurs, the failure occurs in all browsers Edge, IE, and Firefox, so I believe(d) it to be a Excel setting.
As I type this 10:11 CST the webpage opens, we'll see later tonight.

[bknight]

Ugg I'm getting tired of this. Today I get two errors one in the Vix calculation namely that

Code: Select all

Sub CopyCellsFormulas()
Dim dteDateValue As Date
Dim I As Integer, J As Integer, intStartRow As Integer, R As Integer
Dim Lr As Long, Sr As Long, Cntrw As Long
Dim dbl9Value As Double, dbl21Value As Double
Dim Temp() As Variant
Dim rngWs As Range
Dim arrrngWs() As Variant
Dim ws As Worksheet
Dim LastRow As Long
Dim lRow As Long
Dim wsSourceV As Worksheet
Dim wsDestinationV As Worksheet
Dim strDate As String
Dim rVix As Range
Const defName As String = "DataCol"
Const defNameOEX_High As String = "OEX_High"
Const defNameOEX_Low As String = "OEX_Low"
Const defNameDATE_Range As String = "DATE_Range"
' Vix
 Application.DisplayAlerts = False ' suppresses warnings
        For I = Sheets.Count To 1 Step -1
            If Sheets(I).Name = "VixData" Then Sheets(I).Delete
        Next
 Worksheets.Add(After:=Worksheets("Data")).Name = "VixData"
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.cboe.com/publish/scheduledtask/mktdata/datahouse/vixcurrent.csv", Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "daily"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .Refresh BackgroundQuery:=False
        End With
 With Worksheets("VixData")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row - 1
    .Range("A1:A" & LastRow).TextToColumns Destination:=.Range("B1"), Comma:=True
 End With
 Set wsSourceV = Sheets("VixData")
 Set wsDestinationV = Sheets("Vix")
 strDate = Format(Worksheets("VixData").Range("B" & LastRow).Value, "m/d/yyyy")
   With wsDestinationV
   Set rVix = .Columns(1).Cells.Find(What:=strDate, After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

the set statement did not "find" the string date "1/27/2020" when it was clearly present.
The other

Code: Select all

Sub RefreshPutCallRatio()
Dim shSource As Worksheet, shDestination As Worksheet
Dim rgF As Range
Dim strDate As String
Dim lRow As Long
    Set shSource = Sheets("Data")
    Set shDestination = Sheets("PutCall Ratio")
    With shDestination

        strDate = Format(Worksheets("Data").Range("H2").Value, "m/d/yyyy")
        On Error Resume Next
        Set rgF = .Columns(1).Cells.Find(What:=CDate(strDate), After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        On Error GoTo 0
        
        If rgF Is Nothing Then
            MsgBox "Date cannot be found on PutCall Ratio sheet! Record will be added to the end of the table!", vbExclamation
            lRow = .Cells(1).CurrentRegion.Rows.Count + 1
            .Cells(lRow, "A").Value = shSource.Range("H2").Value
            .Cells(lRow, "B").Value = shSource.Range("H3").Value
            .Cells(lRow, "C").Value = shSource.Range("H4").Value
            .Cells(lRow, "D").Value = shSource.Range("H5").Value
            .Cells(lRow, "E").Value = shSource.Range("H6").Value
            If (.Cells(lRow - 1, "B")) - (.Cells(lRow - 1, "C")) < -500 And _
               (.Cells(lRow, "B")) - (.Cells(lRow, "C")) > -500 Then _
               .Range(.Cells(lRow, "B"), .Cells(lRow, "C")).Interior.Color = vbGreen
            .Cells(lRow, "F").Value = "=(R[-1]C*R3C9)+(RC[-1]*R2C9)"
            .Cells(lRow, "G").Value = "=(R[-1]C*R7C9)+(RC[-2]*R6C9)"
            .Cells(lRow, "H").Value = "=RC[-2]/RC[-1]"
            .Cells(lRow, "K").Value = "=AVERAGE(R[-8]C[-6]:R[-1]C[-6])"
            .Cells(lRow, "L").Value = "=AVERAGE(R[-20]C[-7]:R[-1]C[-7])"
            Application.Goto .Cells(lRow, 1), True
        ElseIf Not rgF Is Nothing Then
            lRow = rgF.Row
            .Cells(lRow, "B").Value = shSource.Range("H3").Value
            .Cells(lRow, "C").Value = shSource.Range("H4").Value
            .Cells(lRow, "D").Value = shSource.Range("H5").Value
            .Cells(lRow, "E").Value = shSource.Range("H6").Value
            .Cells(lRow, "F").Value = "=(R[-1]C*R3C9)+(RC[-1]*R2C9)"
            .Cells(lRow, "G").Value = "=(R[-1]C*R7C9)+(RC[-2]*R6C9)"
            .Cells(lRow, "H").Value = "=RC[-2]/RC[-1]"
            .Cells(lRow, "K").Value = "=AVERAGE(R[-8]C[-6]:R[-1]C[-6])"
            .Cells(lRow, "L").Value = "=AVERAGE(R[-20]C[-7]:R[-1]C[-7])"
            Application.Goto .Cells(lRow - 12, 1), True
        End If

    End With

the set rgF could not find the string date "1/27/2020"
I'm not sure what happened as this is new.

Ah I just checked and the cells contained a formula not a value, so this is probably the reason both failed, however in both set statements it indicates to me look in formulas but this doesn't appear to be the case. The formula was to add 3 to the cell above, Fridays date.
The dates have been changed to values and this should fix the same issue from occurring tomorrow.

[Rudi]

Glad you came right.

[bknight]

A blast from the past. Well CBOE did it to me again, changing the manner in which they present data. This one is the VIX that gave so much trouble just accessing the data, well now they have it in a page form much like the Putt/Call data was changed.
The new page has a link as follows: https://markets.cboe.com/delayed_quotes/vix

I don't know how to code in the values of Open, High, Low and Last. If any one would like to help here I'd appreciate that.
For the time being I am entering the data manually and calculating, the whole sheet minus the VIX calculations with the code that was developed. Now comes my issue of the day. I seem to remember a GoTo command long ago, when line identifiers were used. From near the beginning of the code I would like to GoTo a command about here.

Code: Select all

"PutCall Ratio"
Call RefreshPutCallRatio 

.
Would a proper command be GoTo "PutCall Ratio"?
Anyway please help if you are able and have the time.

I am unable to post the code as it has over 7500 characters and I can't attach a .txt document.

[HansV]

You can define labels in a procedure or function, and jump to them from another location in the same procedure or macro.
A label must be a single word (underscores are allowed, but no punctuation or spaces) and it must be preceded by a colon:

:MyLabel

To jump to the line below :MyLabel, use

GoTo MyLabel

[bknight]

Ok.

[HansV]

You should be able to attach a .txt file, by the way.

[bknight]

You can define labels in a procedure or function, and jump to them from another location in the same procedure or macro.
A label must be a single word (underscores are allowed, but no punctuation or spaces) and it must be preceded by a colon:

:MyLabel

To jump to the line below :MyLabel, use

GoTo MyLabel

I am unable to define that label. Every time I enter :PutCallRatio the editor puts in a space after the colon, then I get Sub or Function not defined.

[Doc.AElstein]

IIn VBA, I have never seen a label organised like that. But I may have missed something...

I think as you have it , VBA sees the colon as something like a line separator. So you have two lines there: To the left you have an empty line, so VBA ignores it. To the right it has something it does not recognise. Hence the error you get.

As far as I know, to make a word a label ( or spring point as I think its sometimes called ) you put the colon after it, at the place of the label.

So you want to be using
GoTo PutCallRatio

and then at the point you want to go to ( jump to ) , you need
PutCallRatio:

( I think actually it goes, ( jumps), to the line just after where the label is, as Hans said)

_._____
Instead of a word you could use a number instead. If you use a number then you don’t need a colon after it at the spring point. ***Possibly, technically speaking it is not a spring point or a label.
( If you do put a colon after the number at the spring point, then it wont error. I think then the colon is just interpreted as something like a line separator. So in that case you could put any normal code line after ))

So you could use
GoTo 123

and then at the point you want to go to ( jump to ) , you can do any of these
123

123:

123 MsgBox "Hello"

123: MsgBox "Hello"

In the case of a number, you go to ( jump ) to the code line where the number is.
In the last example using 123: MsgBox "Hello", you go first to the line 123, which is empty.

It seems that in the case of a line number you go to the line number , and for the case of a label you go to the line just after it. So possibly a line number is just a line number rather than a spring point.***
So it seems that you can simply go to a line number.

Combining the two, a label and a line number seems a bit more complicated , the colon gets either interpreted as a line separator or defining a label.
One example, If you did this…_
123 PutCallRatio:
_.. then you can do GoTo PutCallRatio or GoTo 123 , and it appears to always go to the line after 123 PutCallRatio:

_._____________

The short answer to it all I suppose is that you had your colon on the wrong end… I thought that when i caught a quick glance at this thread yesterday, but I thought I might have missed something. I am still not 100% I am right. I expect Hans will tell us for sure later...

[HansV]

Alan is correct, the colon goes AFTER the label. sorry about the confusion.

[bknight]

It did compile.

[bknight]

IIn VBA, I have never seen a label organised like that. But I may have missed something...

I think as you have it , VBA sees the colon as something like a line separator. So you have two lines there: To the left you have an empty line, so VBA ignores it. To the right it has something it does not recognise. Hence the error you get.

As far as I know, to make a word a label ( or spring point as I think its sometimes called ) you put the colon after it, at the place of the label.

So you want to be using
GoTo PutCallRatio

and then at the point you want to go to ( jump to ) , you need
PutCallRatio:

( I think actually it goes, ( jumps), to the line just after where the label is, as Hans said)

_._____
Instead of a word you could use a number instead. If you use a number then you don’t need a colon after it at the spring point. ***Possibly, technically speaking it is not a spring point or a label.
( If you do put a colon after the number at the spring point, then it wont error. I think then the colon is just interpreted as something like a line separator. So in that case you could put any normal code line after ))

So you could use
GoTo 123

and then at the point you want to go to ( jump to ) , you can do any of these
123

123:

123 MsgBox "Hello"

123: MsgBox "Hello"

In the case of a number, you go to ( jump ) to the code line where the number is.
In the last example using 123: MsgBox "Hello", you go first to the line 123, which is empty.

It seems that in the case of a line number you go to the line number , and for the case of a label you go to the line just after it. So possibly a line number is just a line number rather than a spring point.***
So it seems that you can simply go to a line number.

Combining the two, a label and a line number seems a bit more complicated , the colon gets either interpreted as a line separator or defining a label.
One example, If you did this…_
123 PutCallRatio:
_.. then you can do GoTo PutCallRatio or GoTo 123 , and it appears to always go to the line after 123 PutCallRatio:

_._____________

The short answer to it all I suppose is that you had your colon on the wrong end… I thought that when i caught a quick glance at this thread yesterday, but I thought I might have missed something. I am still not 100% I am right. I expect Hans will tell us for sure later...

Thanks Alan and yes that is the way I'd like it to run. The data format changed from a .csv file to a web page as I linked and I don't know how to pick out the numbers from the web page in code. https://markets.cboe.com/delayed_quotes/vix/

[bknight]

The guys on the web site that I have been obtaining the data from have change the format slightly, same web page but now it isn't updated on the current day but the next day and the data isn't coming through. This workbook is very large and I am unable to attach it, but I can copy the code and see if we can work around the issue. This is the part that is giving me fits. The code use to work but as I said they are updating todays data tomorrow instead of late today as they used to do. You will see my attempt early in the code to attempt to compensate for the date difference. That was unsuccessful. As I stepped through this I got an error 2402, I believe the data page was filled with the correct date but the data had #N/A.

The images are of:
The data sheet that contains the data and note the #N/A from this morning's execution.
The PCR sheet has the numbers for yesterday instead of the #N/A.
On the code file you will notice a lot of commented out lines, please disregard as they were attempts that failed.

The website for the data is: https://www.cboe.com/us/options/market_ ... ics/daily/

I don't know where the VIX data is and I and input these numbers along with OEX values.