Formula returns #N/A

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Formula returns #N/A

Post by bknight »

Rudi:
You helped with some code by creating a spreadsheet with code that I use nightly. Well it worked perfectly until Monday, then I get an error. I have included the data sheet for 7 Apr 2021. Now the data is perfect but the date has somehow got corrupted and displays a #N/A instead of the date could you have a look at this for me?
I'll copy the code which isn't on the sheet, I think, to help you.
You will notice the command GoTo PutCallRatio, that to get around the errors created when they deleted the named address and started putting data in a new format. I do this part manually every night.
The file is to large to attach 346K. The code is too long for the character limits. I don't have any cloud to ad the file.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

A Gmail is account is free. It comes with space on Google Drive.
A Microsoft account is free. It comes with space on Onedrive.
The free version of Dropbox comes with 2 GB of cloud storage.

And if you don't want cloud storage, there is We Transfer
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Formula returns #N/A

Post by Doc.AElstein »

I must have about a dozen app.box.com accounts , like we talked about here
http://www.eileenslounge.com/viewtopic. ... 15#p197115
No Ads, never had any spam Emails from them, unlike I did with some other free cloud sharing sites.
( Register free here : https://app.box.com/signup/personal )
I have never had any problem with them, ( and at some of the major forums, where I have to go incognito, I have a matching EMail account , Forum username account and appbox account for a particular area of Excel or anything else I am interested in, and it is a useful way to organise stuff. Its an unconventional, but I find quite practical way, to keep a bit organized )

( More recently on some of my older computers with older Operating systems with out of date browsers, some functions in my app box accounts stopped working, or got very slow, but changing to Firefox browser seemed to sort out all those problems )

_.___

A Gmail account like Hans said is free, easy to register,
( https://accounts.google.com/signup/v2/w ... try=SignUp ) ,
and comes with an automatic cloud storage.
A short cut to get a share link to a large file from the free storage is to send yourself an Email and try to attach the file, then it gets sent as a link to the file storage.

A German Telekom account works the same, sending a large attached file as a link which you can then share, and I have found it is the one of the most easiest to register an account with and so far I never hit a limit with nigh on 100 accounts. I use a lot of those extra accounts purely for the file cloud storage facility, as I do with some of the Gmail accounts. But Gmail seem to limit the number of accounts that they will let be verified via a particular telephone number.. They don’t give the figure officially, but it seems to be about 10-12 in my experience. German Telekom seems to have no limit.
Free mail register at German Telekom is at
https://meinkonto.telekom-dienste.de/te ... tion.xhtml
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Well I do have a Gmail account and it does have MyDrive. Now since I have never used this, it will be a two step process. I have uploaded two files
Data.xls and VIXData050824.xls (the original file). Now how do I allow you to download?

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Thanks Doc, if the MyDrive doesn't work, I'll give app.box a try.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

HansV wrote:
08 Apr 2021, 07:12
A Gmail is account is free. It comes with space on Google Drive.
A Microsoft account is free. It comes with space on Onedrive.
The free version of Dropbox comes with 2 GB of cloud storage.

And if you don't want cloud storage, there is We Transfer
I did(do?) have a drop box account, but the last time we had an issue to discuss they wanted to charge me which is not what I care for, but I understand their need.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

bknight wrote:
08 Apr 2021, 12:39
I have uploaded two files Data.xls and VIXData050824.xls (the original file). Now how do I allow you to download?
Right-click one of the uploaded files.
Select 'Get link' from the context menu.
Click 'Copy Link'.
You can now paste the URL into a reply in this thread.
Do the same for the other uploaded file.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »


User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Something went wrong - when I click on the links, I get a message "you need access"...
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Formula returns #N/A

Post by Doc.AElstein »

Same with me....
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

https://drive.google.com/file/d/1anUNAT ... sp=sharing Not sure if it is the same link
https://drive.google.com/file/d/1eFs6wY ... sp=sharing
I didn't include everyone, sorry, I'm a first time user.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Yep, it works now.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

I have changed the subject of this thread to be more descriptive of the problem and to remove the request to a particular member. See Rule #3 in House rules for Eileen's Lounge
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

The cause of the error is that the text "Cboe Market Summary for" does not occur in column A.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

HansV wrote:
08 Apr 2021, 14:04
The cause of the error is that the text "Cboe Market Summary for" does not occur in column A.
Here is the web link

https://markets.cboe.com/us/options/mar ... ics/daily/

What column does the date appear?

ETA: The rest of the the data is accurate, just the date is the problem as far as I can see.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Apparently they don't mention the date on that web page anymore.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

Look at the web page it is displayed with a small calendar function to the left.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

Ah - I didn't see that because I had scripts turned off.

The little calendar is not a text element, but some kind of control. I don't know anything about such stuff, hopefully someone else can help you with that.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Formula returns #N/A

Post by bknight »

HansV wrote:
08 Apr 2021, 15:00
Ah - I didn't see that because I had scripts turned off.

The little calendar is not a text element, but some kind of control. I don't know anything about such stuff, hopefully someone else can help you with that.
There are too many settings and unless everyone has the same we are on the Tower of Babel. :groan:

In the page refresh that Rudi programmed, there is no date. Would the "exclusion" of the date be in:

Code: Select all

Private Sub GetDataWeb()
    Application.CutCopyMode = False
    On Error Resume Next 'added this to eliminate securty warning alerts
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://markets.cboe.com/us/options/market_statistics/daily", Destination _
        :=Range("$A$2"))
        On Error GoTo 0 'added this to eliminate securty warning alerts
        '.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
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula returns #N/A

Post by HansV »

I don't think so, but I will have to leave this to others. I'm totally out of my depth here.
Best wishes,
Hans