How to check if file exists on web

[mchac]

I would like to be able to check if a file exists and direct VBA execution based on the result. The code I've seen so far in my search all seems to check if a file exists on a local drive. I need to check if the file exists on the web. For example if:

www.sec.gov/Archives/edgar/data/886475/000101905613000180/Financial_Report.xls

exists (which it does) then set a flag to True.

I've tried the Dir command and a couple other things I've seen but unsuccessfully.

Does anyone know of a simple way of doing this in VBA?

Thanks in advance.

 

[mchac]

All done. 16,198 files.
Now for stage 3 which will pull specific balance sheet items for comparison.

 

[rjwebgraphix]

All done. 16,198 files.
Now for stage 3 which will pull specific balance sheet items for comparison.

If I understand what you're needing to do next, this might be a starting point.... http://www.mrexcel.com/forum/excel-questions/696591-visual-basic-applications-excel-2003-a.html

See John's response about a web query. That's probably a good starting point where it will pull data directly from a web page. Not sure how it works myself.

 

[mchac]

Thanks for the link.
I'll have a look.

 

[rjwebgraphix]

Thanks for the link.
I'll have a look.

I got your messages about the xml files. Unfortunately, it says your PM box is full and I can't reply to them. I thought you were going to be pulling them straight from webpages which is what the webquery would do. If doing them with xml files or the xls files, either way, webquery wouldn't help with that. That's probably all back to VBA to do what you need. I'll take a look at them and see if I can figure out what you were talking about for what you need and see if I can put something together that will work. In the meantime..... Your mailbox is full.

 

[mchac]

Yeah, sorry about the mailbox being full. I have kept the code you sent so I can apply it once I can come up for air. Hopefully that will be today but I'm suffering from something that looks like:

actual_date_completed = estimated_date_completed.offset(three days,0).%$#@

 

[mchac]

RJ, I now have version 1 of the listing that I needed and I take it back what I said about not needing the webquery. It turns out that it would be helpful to pull the SIC codes from the SEC website. SIC is Standiard Industrial Code.

I have a list of the SEC CIK codes and if I input that into a web address like the one below (in this case the CIK is 34088 which is the one for Exxon Mobil Corp) and look at the xml of the webpage (right click and View Source but there must be an automated way to do that) I can search for "SIC=" and the four digits next to it (2911) are what I need to pull back for a few thousand firms.

EDGAR Search Results

So now that data ver 1 is in hand I'll have a chance to look over the code you sent and read John's comments in the link above. Thanks again for sending it.

 

[rjwebgraphix]

Check this out. It might help if you do need to web query. It does pull in the information, however, it isn't the quickest if you're doing 17k query's.

You will notice that the information you need is not within a table, which is why it has to pull the entire page, not just the portion you need. That's a bummer because it puts the information you need in one cell for each line of information. You can use string manipulation methods to pull out exactly what you need of it though. Unfortunately, string manipulations Len, Left, Right, Mid, etc are my nemesis, so probably won't be able to provide much help in that regard. There are some guys here that are really good at figuring it out though.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.sec.gov/cgi-bin/browse-edgar?company=&match=&CIK=34088&filenum=&State=&Country=&SIC=&owner=exclude&Find=Find+Companies&action=getcompany", Destination:= _
        Range("A1"))
        .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

 

[mchac]

Thanks very much RJ. I made progress with Norie's suggestions and with John identifying an error I couldn't isolate it resulted in this:

Sub GetSICs()
    
    Application.ScreenUpdating = False
    
    Dim AWBN As String
    Dim ASN As String
    Dim CIK As String
    Dim NUM_FILES_TO_GET As Long
    Dim COUNTER As Long
    Dim SICTagPos As Integer
    Dim SIC As String
    Set IEbrowser = CreateObject("InternetExplorer.application")
    IEbrowser.Visible = False
    
    AWBN = ActiveWorkbook.Name
    ASN = ActiveSheet.Name
    Workbooks(AWBN).Sheets(ASN).Range("A1").Select
    ActiveCell.Offset(0, 11) = "SIC"
    NUM_FILES_TO_GET = Application.WorksheetFunction.CountA(Range("A:A"))
    
    For COUNTER = 1 To NUM_FILES_TO_GET
    
            Application.StatusBar = "Counter = " & COUNTER
            
            SICTagPos = 0
    
            CIK = ActiveCell.Offset(COUNTER, 2)
        
            IEbrowser.Navigate URL:="http://www.sec.gov/edgar/searchedgar/companysearch.html"
            
            Do
                DoEvents
            Loop Until IEbrowser.readyState = 4
            
            Set frm = IEbrowser.Document.forms(0)
        
            frm("CIK").Value = CIK
        
            frm.submit
            
            While IEbrowser.Busy Or IEbrowser.readyState <> 4: DoEvents: Wend
            Application.Wait (Now() + CDate("00:00:01"))
                
            SICTagPos = InStr(1, IEbrowser.Document.body.innerhtml, "SIC=")
                    
            SIC = Right(Left(IEbrowser.Document.body.innerhtml, SICTagPos + 7), 4)
            
    
            
            ActiveCell.Offset(COUNTER, 11) = SIC
            
    
    Next
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
End Sub

It pulls the SIC for about 4500 firms by iteratively pulling the CIK from my spreadsheet and getting the firm's SEC filings as the web reply. I have about 14k reports but after paring it down I have about 4500 firms in my target set.

I was also trying to understand xPath to parse the xml and registered to StackOverflow to try to get some advice. One guy there said that searching for a single string using instr() etc is better than parsing the entire xml to find that attribute.
This may not be the fastest but it gets done in about 30 mins so it's sufficient for my purposes.

Separately, I had a contact send me VBA code to get the specific financial data I want, rather than downloading all the financials, via an XBRL query. Shame for me that wasn't available 2 weeks ago but I learned a lot on this project.

I'm going to play with that tomorrow. Will send you any successes I have in case they prove useful to you.

Thanks again.

 

[testnopassive]

passive-ftp (passive-ftp.com) IS A MESS

it doesn't work, and you will never get your money back

 

[mchac]

it does work but is not the cleanest method.