Function GetInformationForDowFilings() Dim strStartTime As String strStartTime = Now() 'Clear existing information DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE Dow SET EntityRegistrantName = ''" DoCmd.RunSQL "UPDATE Dow SET TradingSymbol = ''" DoCmd.RunSQL "UPDATE Dow SET DocumentPeriodEndDate = ''" DoCmd.RunSQL "UPDATE Dow SET Assets = ''" DoCmd.RunSQL "UPDATE Dow SET LiabilitiesAndStockholdersEquity = ''" DoCmd.RunSQL "UPDATE Dow SET CashAndCashEquivalentsPeriodIncreaseDecrease = ''" DoCmd.SetWarnings True Dim strUseURL As String Dim strSeakConcept As String Dim rs As Recordset 'Set rs = CurrentDb().OpenRecordset("SELECT * FROM Dow WHERE Ticker = 'MCD'") Set rs = CurrentDb().OpenRecordset("SELECT * FROM Dow") Do While Not rs.EOF 'Debug.Print rs.AbsolutePosition DoEvents Set oDocument = Nothing Set oDocument = New MSXML2.DOMDocument oDocument.async = False oDocument.validateOnParse = False oDocument.Load (rs!LinkToXBRLInstance) If oDocument.parseError.ErrorCode <> 0 Then Debug.Print "Error loading file: " & vbCrLf & vbCrLf & _ "File URL: " & oDocument.parseError.url & vbCrLf & _ "Error Code: " & oDocument.parseError.ErrorCode & vbCrLf & _ "Error Description: " & oDocument.parseError.reason & vbCrLf _ , vbCritical End If 'oDocument.SetProperty "SelectionLanguage", "XPath" Dim oNodelist As MSXML2.IXMLDOMNodeList Dim oNode As MSXML2.IXMLDOMNode Dim strNetwork As String Dim strTable As String Dim strValue As String Debug.Print "Filing: " & rs.AbsolutePosition + 1 Dim strDocument As String Dim strTaxonomyVersion As String strTaxonomyVersion = "ERROR" strDocument = oDocument.XML '0=not found; some number is the position 'This differentiates between the 2009 and the 2011 US GAAP taxonomies... 'Debug.Print InStr(1, strDocument, "http://xbrl.us/us-gaap/2009-01-31") If InStr(1, strDocument, "http://fasb.org/us-gaap/2011-01-31") = 0 Then 'This is NOT the 2011 taxonomy strTaxonomyVersion = "http://xbrl.us/us-gaap/2009-01-31" oDocument.SetProperty "SelectionNamespaces", "xmlns:xbrli='http://www.xbrl.org/2003/instance' xmlns='http://www.xbrl.org/2003/instance' xmlns:xbrldi='http://xbrl.org/2006/xbrldi' xmlns:us-gaap='http://xbrl.us/us-gaap/2009-01-31' xmlns:dei='http://xbrl.us/dei/2009-01-31'" Else 'This IS the 2011 taxonomy strTaxonomyVersion = "http://fasb.org/us-gaap/2011-01-31" oDocument.SetProperty "SelectionNamespaces", "xmlns:xbrli='http://www.xbrl.org/2003/instance' xmlns='http://www.xbrl.org/2003/instance' xmlns:xbrldi='http://xbrl.org/2006/xbrldi' xmlns:us-gaap='http://fasb.org/us-gaap/2011-01-31' xmlns:dei='http://xbrl.sec.gov/dei/2011-01-31'" End If 'EntityRegistrantName strSeekConcept = "dei:EntityRegistrantName" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef][0]") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET EntityRegistrantName = '" & oDocument.selectSingleNode("//dei:EntityRegistrantName").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If 'DocumentPeriodEndDate strSeekConcept = "dei:DocumentPeriodEndDate" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef][0]") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET DocumentPeriodEndDate = '" & oDocument.selectSingleNode("//dei:DocumentPeriodEndDate").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If 'dei:TradingSymbol 'Note that not every filer provides their trading symbol... strSeekConcept = "dei:TradingSymbol" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef][0]") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET TradingSymbol = '" & oDocument.selectSingleNode("//dei:TradingSymbol").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If 'This finds the reporting period Dim strPeriod As String strPeriod = "" strPeriod = oDocument.selectSingleNode("//dei:DocumentPeriodEndDate").Text Dim oNodelist2 As MSXML2.IXMLDOMNodeList Set oNodelist2 = oDocument.selectNodes("//us-gaap:Assets") Dim oNodelist3 As MSXML2.IXMLDOMNodeList 'Nodelist of all the facts which are us-gaap:Assets For i = 0 To oNodelist2.length - 1 'Debug.Print oNodelist2(i).XML strContextID = "" strContextID = oNodelist2.Item(i).selectSingleNode("@contextRef").Text strContextPeriod = oDocument.selectSingleNode("//xbrli:context[@id='" & strContextID & "']/xbrli:period/xbrli:instant").Text 'Debug.Print strContextPeriod 'Nodelist of all the contexts of the fact us-gaap:Assets Set oNodelist3 = oDocument.selectNodes("//xbrli:context[@id='" & strContextID & "']") For j = 0 To oNodelist3.length - 1 'Nodes with the right period If oNodelist3(j).selectSingleNode("xbrli:period/xbrli:instant").Text = strPeriod Then Dim oNode4 As MSXML2.IXMLDOMNode 'Set oNode4 = oNodelist3(j).selectSingleNode("xbrli:entity/xbrli:segment/xbrldi:explicitMember[@dimension='us-gaap:StatementBusinessSegmentsAxis | us-gaap:ScheduleOfAvailableForSaleSecuritiesMajorTypesOfDebtAndEquitySecuritiesAxis | us-gaap:DebtSecurityAxis | us-gaap:FairValueByAssetClassAxis | us-gaap:FiniteLivedIntangibleAssetsByMajorClassAxis']") Set oNode4 = oNodelist3(j).selectSingleNode("xbrli:entity/xbrli:segment/xbrldi:explicitMember") On Error GoTo ErrorHandler Dim strTest As String strTest = 0 strTest = Len(oNode4.Text) 'Debug.Print "Length" & strTest If Not (strTest = 0) Then 'Not the right context 'Debug.Print "Note4: " & oNode4.Text Else 'Debug.Print oNodelist3(j).XML strUsecontext = strContextID 'Debug.Print strUseContext End If 'Debug.Print oNodelist3(j).XML End If Next j Debug.Print " " Next i 'Assets strSeekConcept = "us-gaap:Assets" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET Assets = '" & oDocument.selectSingleNode("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If 'LiabilitiesAndStockholdersEquity 'Hard code the context to adjust for the funky axis used by Bank of America If rs!Ticker = "BAC" Then strUsecontext = "BalanceAsOf_30Jun2011_Scenario_Previously_Reported_Member" End If strSeekConcept = "us-gaap:LiabilitiesAndStockholdersEquity" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET LiabilitiesAndStockholdersEquity = '" & oDocument.selectSingleNode("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If 'This may work incorrectly for fiscal year ends because the dates cross calendar years 'Get context ID of durations Set oNodelist2 = oDocument.selectNodes("//us-gaap:CashAndCashEquivalentsPeriodIncreaseDecrease") Dim strStartDate As String Dim strStartDateYTD As String strStartDate = "ERROR" strStartDateYTD = "2099-01-01" strUsecontext = "ERROR" strContextID = "" For i = 0 To oNodelist2.length - 1 'Debug.Print oNodelist2(i).XML strContextID = oNodelist2.Item(i).selectSingleNode("@contextRef").Text strContextPeriod = oDocument.selectSingleNode("//xbrli:context[@id='" & strContextID & "']/xbrli:period/xbrli:endDate").Text 'strUsecontext = strContextID 'Debug.Print strContextPeriod 'Nodelist of all the contexts of the fact us-gaap:Assets Set oNodelist3 = oDocument.selectNodes("//xbrli:context[@id='" & strContextID & "']") For j = 0 To oNodelist3.length - 1 'Nodes with the right period If oNodelist3(j).selectSingleNode("xbrli:period/xbrli:endDate").Text = strPeriod Then Set oNode4 = oNodelist3(j).selectSingleNode("xbrli:entity/xbrli:segment/xbrldi:explicitMember") On Error GoTo ErrorHandler strTest = 0 strTest = Len(oNode4.Text) 'Debug.Print "Length" & strTest If Not (strTest = 0) Then 'Not the right context 'Debug.Print "Note4: " & oNode4.Text Else Debug.Print oNodelist3(j).XML 'Get the year-to-date context, not the current period strStartDate = oNodelist3(j).selectSingleNode("xbrli:period/xbrli:startDate").Text Debug.Print "Context start date: " & strStartDate Debug.Print "YTD start date: " & strStartDateYTD 'MsgBox "Start date: " & CDate(strStartDate) & " YTD: " & CDate(strStartDateYTD) If CDate(strStartDate) <= CDate(strStartDateYTD) Then 'MsgBox "YTD is greater" 'Start date is for quarter Debug.Print "Context start date is less than current year to date, replace" Debug.Print "Context start date: " & CDate(strStartDate) 'Debug.Print "Current min: " & CDate(strStartDateYTD) strStartDateYTD = strStartDate strUsecontext = oNodelist3(j).selectSingleNode("@id").Text 'MsgBox oNodelist3(j).selectSingleNode("@id").Text Else 'MsgBox "Context is greater" 'Start date is for year Debug.Print "Context start date is greater than YTD, keep current YTD" Debug.Print "Context start date: " & CDate(strStartDate) strStartDateYTD = strStartDateYTD strUsecontext = strUsecontext End If 'Debug.Print "Use context: " & strUseContext Debug.Print "Current min: " & CDate(strStartDateYTD) Debug.Print " " End If Debug.Print "Use context: " & strUsecontext 'MsgBox strUsecontext End If Next j Debug.Print " " Next i 'CashAndCashEquivalentsPeriodIncreaseDecrease strSeekConcept = "us-gaap:CashAndCashEquivalentsPeriodIncreaseDecrease" Set oNodelist = oDocument.selectNodes("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']") 'MsgBox oNodeList.length 'Debug.Print "Filing #: " & rs.AbsolutePosition + 1 & "; Found concepts: " & oNodeList.length If oNodelist.length > 0 Then DoCmd.SetWarnings False DoCmd.RunSQL ("UPDATE Dow SET CashAndCashEquivalentsPeriodIncreaseDecrease = '" & oDocument.selectSingleNode("//" & strSeekConcept & "[@contextRef='" & strUsecontext & "']").Text & "' WHERE LinkToXBRLInstance = '" & rs!LinkToXBRLInstance & "'") DoCmd.SetWarnings True End If rs.MoveNext Loop Debug.Print "Started: " & strStartTime & " , completed: " & Now() Exit Function ErrorHandler: Debug.Print "Error number " & Err.Number & ": " & Err.Description Resume Next 'MsgBox "Done" End Function