ࡱ> <;  !"#$%&'()*+,-./0123456789:JM>?@ABCDEFGHILNOQ`STUVWXYZ[\]^_Kac|epghijklmnoqrstuvwxyz{P}~Root Entry FK(=$Workbookq_VBA_PROJECT_CUR"𭲩ԩVBA ũ \pblhodg00 Ba= ThisWorkbook=O 28X@"1Arial1RCalibri1RCalibri1RCalibri1"R Helvetica1RArial1RArial1RArial1"R Helvetica1RArial1RArial1xRArial1xRArial1RArial1h8RCambria1,8RCalibri18RCalibri18RCalibri1RCalibri1RCalibri1<RCalibri1>RCalibri1?RCalibri14RCalibri14RCalibri1 RCalibri1 RCalibri1RCalibri1RCalibri1 RCalibri"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) \(#,##0\)@*."*"#,##0;"*"\-#,##0;"*"@                                                                      ff + ) , *     P  P        `           "@ @   "@ @  !   a>  !8 !8 ! ! !8   8@ @  8@ @  8@ @  " @ @  ",@ @  ",@ @  !8@ @   "<@ @   ,@ @   l@ @   H  ,@ @      #    ! #<@ @  #<@ @  #<@ @  #|@ @  #|@ @    H "L@ @  8@ @  8@ @  8@ @  8@ @  8@ @  8@ @   !8  !8 ||fe}-}> 00\);_(*}A}1 00\);_(*;_(@_) }A}2 00\);_(*?;_(@_) }A}3 00\);_(*23;_(@_) }-}4 00\);_(*}A}0 a00\);_(*;_(@_) }A}( 00\);_(*;_(@_) }A}7 e00\);_(*;_(@_) }}5 ??v00\);_(*̙;_(@_)    }}9 ???00\);_(*;_(@_) ??? ??? ??? ???}}) }00\);_(*;_(@_)    }A}6 }00\);_(*;_(@_) }}* 00\);_(*;_(@_) ??? ??? ??? ???}-}@ 00\);_(*}x}800\);_(*;_(??? ??? ???}-}/ 00\);_(*}U}? 00\);_(*;_( }A}" 00\);_(*;_(}A} 00\);_(*ef;_(}A} 00\);_(*L;_(}A} 00\);_(*23;_(}A}# 00\);_(*;_(}A} 00\);_(*ef;_(}A} 00\);_(*L;_(}A} 00\);_(*23;_(}A}$ 00\);_(*;_(}A} 00\);_(*ef;_(}A} 00\);_(*L;_(}A} 00\);_(*23;_(}A}% 00\);_(*;_(}A} 00\);_(*ef;_(}A} 00\);_(*L;_(}A} 00\);_(*23;_(}A}& 00\);_(*;_(}A} 00\);_(*ef;_(}A} 00\);_(*L;_(}A}  00\);_(*23;_(}A}' 00\);_(* ;_(}A} 00\);_(*ef ;_(}A} 00\);_(*L ;_(}A}! 00\);_(*23 ;_( 20% - Accent1M 20% - Accent1 ef % 20% - Accent2M" 20% - Accent2 ef % 20% - Accent3M& 20% - Accent3 ef % 20% - Accent4M* 20% - Accent4 ef % 20% - Accent5M. 20% - Accent5 ef % 20% - Accent6M2 20% - Accent6  ef % 40% - Accent1M 40% - Accent1 L % 40% - Accent2M# 40% - Accent2 L湸 % 40% - Accent3M' 40% - Accent3 L % 40% - Accent4M+ 40% - Accent4 L % 40% - Accent5M/ 40% - Accent5 L % 40% - Accent6M3 40% - Accent6  Lմ % 60% - Accent1M 60% - Accent1 23 % 60% - Accent2M$ 60% - Accent2 23ٗ % 60% - Accent3M( 60% - Accent3 23֚ % 60% - Accent4M, 60% - Accent4 23 % 60% - Accent5M0 60% - Accent5 23 %! 60% - Accent6M4 60% - Accent6  23 % "Accent1AAccent1 O % #Accent2A!Accent2 PM % $Accent3A%Accent3 Y % %Accent4A)Accent4 d % &Accent5A-Accent5 K % 'Accent6A1Accent6  F %(Bad9Bad  %) Calculation Calculation  }% * Check Cell Check Cell  %????????? ???+ Comma,( Comma [0]-&Currency.. Currency [0]/Explanatory TextG5Explanatory Text % 0Good;Good  a%1 Heading 1G Heading 1 I}%O2 Heading 2G Heading 2 I}%?3 Heading 3G Heading 3 I}%234 Heading 49 Heading 4 I}% 5InputuInput ̙ ??v% 6 Linked CellK Linked Cell }% 7NeutralANeutral  e%"Normal 8Noteb Note   9OutputwOutput  ???%????????? ???:$Percent;style_col_headings<style_col_numbers= style_titles >Title1Title I}% ?TotalMTotal %OO@ Warning Text? Warning Text %XTableStyleMedium9PivotStyleLight16`'<Line amounts by sectorst B=\Table 1. Corporations Claiming a Credit for Increasing Research Activities on Form 6765 [2]W[All figures are estimates based on samples--money amounts are in thousands of dollars]Item All sectorsSectors+Agriculture, forestry, fishing, and huntingMining Utilities Construction ManufacturingWholesale and retail tradeTransportation and warehousing InformationFinance and insurance Real estate, rental, and leasing0Professional, scientific, and technical services+Management of companies (holding companies)5Administrative/ support and waste management servicesVarious services [8] 4Number of Returns Claiming a Credit on Form 6765 [3]Section A-Regular Credit. [7] 71 Certain amounts paid/incurred to energy consortia.2 Basic research payments paid or incurred/3 Qualified organization base period amount"5 Wages for qualified services6 Cost of supplies(7 Rental or lease costs of computers88 Applicable percentage of contract research expense49 Total qualified research expenses (sum 5-8) [4]&11 Average annual gross receipts [5]12 Base amount17 Regular Credit [6].20 Qualified organization base period amount!24 Wages for qualified services25 Cost of suppliesNotes:c Estimate should be used with caution because of the small number of returns on which it was based."[1] This amount is less than $500.\[2] Includes returns of active corporations, other than Forms 1120S, 1120-REIT, and 1120-RIC[7] Component data for each section also includes amounts for those credit claimants not claiming the specific section credit. [8] "Various Services" include educational services; health care and social assistance; arts, entertainment, and recreation; accommodoation and food services; and other services.918 Certain amounts paid or incurred to energy consortia719 Basic research payments to qualified organizations'26 Rental or lease costs of computers?27 Enter the applicable percent of contract research expenses929 Enter total qualified research for prior 3 tax years@37 Passthrough rsrch cr(s) from prtnshp, S corp, est, or trust628 Total qualified research expenses (sum 24-27) [4]&34 Alternative Simplified Credit [6]B38 Current-year credit for Increasing Research Activities [3][6]}[3] Number of credit claimants and Line 38 amount both include corporations that only reported data on Line(s) 37 and/or 38.,Section B-Alternative Simplified Credit. [7]( Section C-Current-Year Credit.[5] Line 11 , Average annual gross receipts, are defined as the average annual gross receipts for the 4 tax years preceding the tax year for which the credit is being determined.[4] Line 9 and Section B line 28 do not equal the sum of their components as shown in this table, because some corporations only reported these total lines.[6] Line 17 and Section B line 34, and line 38 include credit amounts for which some corporations did not provide component data.BSource: Statistics of Income Division: 2010 Corporate Returns Data[1][5] Tax Year 2010Selected Items, by SectorsB. 0'0h2y3{57: ccB4c =_MA  .|\tmp  dMbP?_*+% &R&P of &N&?'?(?)?M*\\DCI0150BPSOI08\DodgerLC odXXLetterDINU"(d dc<C(SMTJHP LaserJet 4250 PCL 6InputBinFORMSOURCERESDLLUniresDLLResolution600dpiFastResTrueOrientationPORTRAITHPOrientRotate180FalseDuplexNONEHPDuplicateJobNameOverrideSWFWHPDocUISUITruePaperSizeLETTERMediaTypeAUTOESPRITSupportedTrueCollateOFFOutputBinAutoStaplingNoneHPPaperSizeALMConstraintsENV_10EconomodeFalseTextAsBlackFalseHPEnableRAWSpoolingTrueTTAsBitmapsSettingTTModeOutlineRETChoiceTrueAlternateLetterHeadFalsePrintQualityGroupPQGroup_1HPDocPropResourceDatahpchl5r1.cabHPColorModeMONOCHROME_MODEHPContentOrientationHPContentOrientation_OptionHPPDLTypePDL_PCL6HPPJLEncodingUTF8HPJobAccountingHPJOBACCT_JOBACNTHPJobByJobOverrideJBJOHPPCL6PassThroughTrueHPSmartDuplexSinglePageJobTrueHPSmartDuplexOddPageJobTrueHPManualDuplexDialogItemsInstructionID_01_FACEDOWN-NOROTATEHPPaperSizeDuplexConstraintsA5HPMediaTypeDuplexConstraintsEXTRA_HEAVYHPManualFeedOrientationFACEDOWNHPOutputBinOrientationFACEDOWNHPManualDuplexDialogModelModelessHPManualDuplexPageOrderEvenPagesFirstHPMapManualFeedToTray1FalseHPManualDuplexPageRotateDriverRotateHPCustomDUplexableRange5.83x8.27_8.5x14HPPrintOnBothSidesManuallyFalseHPStraightPaperPathFalsePSAlignmentFileHPCLS5r1HPSmartHub_OnlinediagnostictoolsTRUEHPSmartHub_SupportandtroubleshootingTRUEHPSmartHub_ProductmanualsTRUEHPSmartHub_CheckfordriverupdatesTRUEPSServices_DeviceandSuppliesStatusTRUEHPSmartHubInet_SID_263_BID_276_HID_265PSServicesOptionPrnStat_SID_242_BID_270_HID_15521HPNUseDiffFirstPageChoiceTRUEHPPageExceptionsFileHPCPE5r1HPPageExceptionsInterfaceShowPageExceptionsHPPageExceptionsLowEndHPPageExceptionsLowEndVerHPPageExceptionsCoverInsertionJRConstraintsJRCHDPartialJRHDInstalledJRHDOffJRHDNotInstalledJRHDOffHPJobAccWoPinTrueHPConsumerCustomPaperHPCustomHPPreAnalysisTrueHPBornOnDateHPBODHPXMLFileUsedhpc42506.xmlHPLpiSelectionNoneIUPHxkAǿۀ?hDEo 9+v3Y7-ԃi $-[7y y3=T0{4<.)X6ԯzZP HΉq`B?qb:IG uJp]h!񘟪xa߈0 *wNJvݣmHDZN|#u|cEaSX@XAXđA O-`XSHX(AXz rX XOAXF'4XTPX@ AXX5X AX X@X7X, R0`Yb<.Y@Y@Y@@Y@Y2YAY@Y6)Y`@Y@Y(UAY@Y@YQ@ N4$XXXXXXXXXXXXXXXDl6666Nnr6rrrrrrrr6rrrrrrrr F!F"S#S$S%S&@'@(S)@*S+S,S-S O.` Xt AX@X0y@Xh@Xx@X@X@X@X@X @XЄ@X@Xu@X(@XP@ !R1`!Y:tY@Y@Y`K@Y@YSY#AY@Y`.AY@AY@$@Y0Y@Y @Y@ "S8"T"T" T#T#T# T $S# %U$ &S% 'S& (V2 )\6 *V5*T* T +V7 ,V' -V( rr,">@dSRA  Sheet15ggD ThisWorkbook Sheet15 Module3 Module4@  !"#$%&'()*+,-./0123456789:;<=>?ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefhijklmnopqrsuvwyz{|}~P#xME (SLSS<N0{00020819-0000-0000-C000-000000000046}8(%HxAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2@#xME (SLSS<N0{00020820-0000-0000-C000-000000000046}8(%HxAttribute VB_Name = "Sheet15" Basx0{00020820- C$0046} |GlobaBlSpacFalse dCre atablPr@edeclaI"dTru BExposeTemplateDer iv$BustomizD2dkyxMEP 0@Ps "   608h"  \ PX      x ( oso $!&'"do ( "loop through all rowsR * "loop through all columns ( *$"** ( *$"$* ( *$, ( *$" ( *$" $09.;(strip the single asterisk off the number"*"#,##0;"*"-#,##0;"*"@92=*format number to display a single asteriskqPkHk@ * ( ( oAttribute VB_Name = "Module3" Option Base 1 Sub removeWeakEstimateIndicators()JAppl v.ScreenUpdatingFalse arrActiveSheet.UsedRang For i 1 To UB ound(2, 1) 'loop through all rows 5k525column If 0(i, k) <> "**" T8hen,LeftCF With Cells"$ .ValuRigh(Len0) -xsҁxe sle asterisk offe number=2N """*""#,##0;-@" 0'forA  to display a IEnd ;IfNext k iTru@A dk.@xMEP 6!!0@Ps!"    6(8`"  \ PP      68 X        (8H ( \G $!&'"Ne ( "loop through all rows * "loop through all columns ( *$"** ( *$"$** ( *$,$, ( *$" ( *$" $09.;(strip the single asterisk off the numberj$ "*"#,##0;"*"-#,##0;"*"@92=*format number to display a single asteriskqXkPkH *0CO (PDF $!&'8 : 8loop through all rows < 8loop through all columns : <$8- : <$8 - : <$8 - : <$8 - : <$8 - : <$,9.qk < : ( oAttribute VB_Name = "Module4" Sub WebReady() Application.Scr@eenUpdngrFalse  arrActiveSheet.UsedRang8 For i@1 To U@Bound(2, 1) 'loop through all ro@ws 5k525column8 If `(i, k) <> "**" pThenXLeftCF With Ce8lls"$ .ValuRig"h(Len0) -xsxe single asterisk offe numhber2N mat"""*""#,##0;-@" 'for& to display a IEnd ;IfNext k i\2%ZL2h 2(g, h)"-" Or T Æ U0_ Za ^h`gq^Tru@| 10* pHd VBAProject4@j = Module1 R#Module2flModule5d'#Module6 v$le )2,3,4,5*R5)we you creating?ATab# berA 'If ""c04" Then@hextraitems" left or join (sel scpl,,omp_div_frn_@p+nvl(cfc_85,0))  othr_rcpts- l_plcy_cashB , e w& a.retuAtype "'08','14@20') t c#tx_exmt_intel_m1 end from micro_" &H& " aA!@trolM us5@](F) @Aid%s b , edit_1_2lform_1h120-c4 're& &1%A'23h'E$B5"BW"c05 !X:7iU:|If Y@h` @2 <> 0 ne`dcm\a Fcm_pos @ i i,ndW"HUsoiBbcd#.42@.44ET'41'cZU 6a367!7 !81900'~ YX60" OrD7")sA'nt|S@f"8l9f'!@`iA(It= '801'! $K 10, 1110`]waoMvdnyMmentity_ad@q al_'tG_{taxmp/@1('12' =41O4 0 oajjj)!.2- 2 FOWNR_CD0EVPRCNT >=x 50CQBEs8lishAsaDne2 toda`"With2 .Proa5r MSDAOXRA.2( PneSnga1 xID=u u;栂w"; D@ Source=SP11.WORLDdOp!=E A P&n.CursorLocp0= ad`Cli=u qF0i 4 To#IP!iP;a ep"DIV_K/ d"(‰ĐϒÒ(}AS:W! ' Ce`R1@` WWsql%#%n,1)-dy`op(*) ctpum(weight_f) wrd(spk*F)A&1^ 72"T6765-a3𨁨G?B_P heǿT 0 ne`dcm\a Fcm_pxos @ i 0 indW" soi_maj_8cd  6"X Orij7I+ sAntn 2f"8l9f'!@RiAIf= '01'! $= 10, 1110`Oa;a?vd`y?mentity_ad@cal_'f#_mtaxmp_ ST!2: a2(+25' or F("T$'Y'a.RETURN_TYPE nottO9', '105R4<)'"12}m3k#4I#72`C0V06Vo$0.1pdJ.1 D^.%'02' a'1=41O4 0accck@2 2 FOWNR_CD DVPRCNT >= 500GJBEs8lisI}heQ#ne2 toam daa"With2 .Proa5rQMSDAORA.V1 2( PnS0nga1 ID=n n;時Cp"; D@ Source=SP11.WORLDgOp!0E A P&n.CursorLocpp= ad`CliW=u qF0i 4 To#IP{!iP!a ^p"DIV_K/ d"(‚ĉϋËq~ xME(<<< <NRʀDp ʀH p(H x Jbb L*% P,% ʀ@H ʀB ʀF( `x x( PPsd "( 08 $@"h  && & Z8     p    *     *  0 2( 0` < v 0H Fx 0 6 0( BX 0 L .0 ` h L       * <P "   ,   x   , $ ( ,8 ,h ,     6  H Xh p x]  Hp(x]] User/SchemaUserID$V'TPasswordPassword$V'XM~1* $!&!\!^'Zec $!&!b!^'`$, ` Z$,$f'dn$, `$,$f'@what year? (cYR)year$V'HstStabNum = InputBox("Which table (1,2,3,4,5, CR5) are you creating?", "Table Number")ingle If sbyear = "c04" ThenH extraitems = " left outer join (select scpl, (comp_div_frn_corp+nvl(div_cfc_85,0)) div_frn, (comp_othr_rcpts-nvl(l_plcy_cash,0)) othr_rcpts, case when a.return_type in ('08','14','20') then comp_tx_exmt_intrst else tx_exmt_intrst_m1 end exmt_intrst from micro_" & sbyear & " a left outer join control_" & sbyear & " using(scpl) left outer join dividends_" & sbyear & " b using(scpl, edit_1_2) left outer join lform_1120_" & sbyear & " c using(scpl, edit_1_2) where edit_1_2 in ('1','A')) using(scpl)"ElseIf sbyear = "c05" Then extraitems = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) where edit_1_2 in ('1','A')"Else: extraitems = ""End IfIf tabNum Mod 2 <> 0 Then$ netincm = "and net_incm_pos > 0"Else: netincm = ""End If| case when soi_div_cd in ('42','44') then '41' when soi_div_cd in ('61','62','71','72','81') then '900' else soi_div_cd end 'h$If tabNum = "6" Or tabNum = "7" Then constraints = "")'ElseIf tabNum = "8" Or tabNum = "9" Then+' constraints = "and return_type = '01'"*ElseIf tabNum = "10" Or tabNum = "11" Then5 items10_11 = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) left outer join entity_additional_c05 b using(scpl, edit_1_2) left outer join form_1120_c05 using(scpl, edit_1_2) left outer join tax_comp_c05 using(scpl, edit_1_2) where edit_1_2 in ('1','A')"o constraints = " and (a.return_type = '05' or (frn_cd = 'Y' and a.RETURN_TYPE not in ('09', '10', '14'))) "*ElseIf tabNum = "12" Or tabNum = "13" Then@ constraints = " and a.return_type not in ('02','04','06') "*ElseIf tabNum = "14" Or tabNum = "15" Then/ constraints = " and a.return_type = '02' "*ElseIf tabNum = "16" Or tabNum = "17" Then< constraints = " and a.return_type in ('08','20','14') "*ElseIf tabNum = "24" Or tabNum = "25" ThenE constraints = " and FRN_OWNR_CD = 'Y' and FRN_OWNR_PRCNT >= 50 "End If(Establish the connection to the database L MSDAORA.19jUser ID= T ; Password= X; Data Source=SP11.WORLD9lC@q  p L(n ( Z ($d'r# If arr1(1, i) = "DIV_FRN" Then5 Item = "(comp_div_frn_corp+nvl(div_cfc_85,0))" Else: Item = arr1(1, i) End If Create a Recordset&If tabNum = "10" Or tabNum = "11" Then select nvl( h3,1) indy, count(*) ct, sum(weight_f) wt, round(sum( r!*weight_f)) theitem from control_ H join form_6765_ H$ a using (scpl,edit_1_2) join micro_ H b using (scpl) where edit_1_2 in ('1','A') and ed_st in ('C','D') and b.return_type not in ('02','04','06') and a.R_TOT_ALLW_RSRCH_CR !=0 and  r!=0 group by rollup( h) Order by indy nulls last'tElse: SQLstring = "select " & indy_cd & " indy_cd, count(*) ct, sum(weight_f) wt, round(sum(" & Item & "*weight_f)) theitem from micro_" & sbyear & extraitems & " and round(" & Item & "*weight_f) <> 0 " & netincm & constraints & " group by rollup(soi_div_cd,case soi_maj_cd when '625' then '6215' when '626' then '622' when '521' then '520' when '522' then '520' else soi_maj_cd end) Order by " & indy_cd & " nulls first"End If.P tx |z L%v.P @ @ @@ ~ @ ~ ($d ~ @+@ P!theitem$P ~ @$@ct$P!. ~ @ +@wt$P!. ~ @ +@ PB@k dFyk ~ ( @$, @ @$,+f PB@B LB@B '@oͷAttribute VB_Name = "Module1" Option Base 1 Public myArray2, s, ac, tabNumbyearNSub arrESV() Dim conn As New ADODBd.Cecg!r st Recordset   UserIdInput Box("/Schema", 1ID"t4 PasswE 1 last@ColumnAMveSheet.3dRangeˀZsunN8Row  1$(Cells(1, 1), &, D.)M"3 sɀwhat ? (cYRn)@K'r Whichx UAS: ' Ce`R1@` WW sql%f1o(*) ct@um(weigh@t_f) wrd(spr*F)%]  O"sRT6765a~׋Ң$ heOC" 0 ne`dcm\a Fcm_pxos @ i indW"HUHsoiBbcd#.42@.44ET'41V'cZ 6a367%!7!81900' YX 6" Or,7")sA'ntO|@f"8l9}f'!@`iA(It= '01'! $K d10, 1110`]aoMvdnyMmentity_ad@qal_'t_{taxySmp/@1('1= 50C0NQBEs8lishAsQ#ne2xME(<<< <N$R.ʀDp ʀH p(xH  cc L % P% ʀ@H ʀB ʀF( `x x( PPs xxME HH@8 OX  HPsI"    @ `p x    J86@ x 2 Xh ( R &Hpx R(N  Np    2HPp2, $ @P X hp R@  (x $!&'   ( WA  (  $' (  $'dX ( $'L (  $'k (  $'r ($'+Bu r ** r**4Gthis will change (**) in major or sector to (-) if no data exists in AC-'rorpk` r !%N gDItem = Format(Application.WorksheetFunction.Round(Item, 0), "#,##0") r !%'rd r r  r r [1]'rdp,***make negative "stored" items positive ***  $NET_GN_LSS_NEG  $ NET_INCM_NEG  $DEFICIT  $SDEFICIT QItem = Format(Application.WorksheetFunction.Round(Abs(Item) * 0.001, 0), "#,##0") rMbP? !%'rd LItem = Format(Application.WorksheetFunction.Round(Item * 0.001, 0), "#,##0") rMbP? !%'rkkkxK* Place an (*) in front of the number if it is a weak estimate **    $ $ * r##,##0 !%d'rGhardcode a comma in this numberHItem = "*" & Format(Item, "###") 'if only want an "*" with out the commak8k0k( r (  + ( ]@ $Remove the columns with counts/wt-ct '8 @8x (  ($  ($COUNT(*) 8 8 @8 *  * ($ * 8+8 *k ( 8' $!&B@KRange(Cells(1, 1), Cells(UBound(myarray, 1), UBound(myarray, 2))) = myarray  %$,  $,+foAttribute VB_Name = "Module2" Option Base 1 Sub markESV() myarrayjActiveSheet.UsedRange @ For x@1 To UBound(l, 2) Step 3M )i)2)1p IfMhen5 itemCt;Y(i,&+ 58"W"2,ElsW&'%7EndTNI:K9totwe H31 q0 Or (<> "**" A.#+) T 'this will ch (**) inj!sectto (- f no data exists ACT9D"-2%Ap plicaA.Works{Funon.IsNumber(%@ l'D|mat( RŒA , 0), "#,##0"cD%{@[, > 0W< 5X00)_B< >| -BA^A!"8[1]@MA'***make negOve "sbed"Bs phosi f: E1C@ˀNET_GN_LSS`_NEG"( INCM[ DEFICITS&8etFCc8AbsdC* 0.00:A' ):d( (g0 n"em|dCM &f< &AHC Place an{{front o0f thKo i fJ a weak estilveK`*A^< 1^`G=cpWt)d-)'I9"@& ;$Text$|| 'hardcod comman( ;# D7 #") ' #only wa&(Awith out"( # 0%s! N@ ixDim 2 'Remo`!5columncts/wt-ct bB=e Re!P`reser(1π V#:ȅi) EmptyO"COUNT(*)"rf[/ 2% ,pw,ߵ~xq k3OyS#(k.) i` }kيQ  ?0.ClearCP5ents  '(Cells(KOf _0ys2wTrans0de  1(1e "( 08 $@"h  && & Z8     p    *     *8 h 0 2 0 <  v` 0 F 0P 6 0 B 00 L` .   L ` h  p    * < " H X ,x  h  ( 0 ,P $  , , ,8 h x   6      x]  Hp(x]] User/SchemaUserID$V'TPasswordPassword$V'XM~1* $!&!\!^'Zec $!&!b!^'`$, ` Z$,$f'dn$, `$,$f'@what year? (cYR)year$V'HStabNum = InputBox("Which table (1,2,3,4,5, CR5) are you creating?", "Table Number")If sbyear = "c04" Then extraitems = " left outer join (select scpl, (comp_div_frn_corp+nvl(div_cfc_85,0)) div_frn, (comp_othr_rcpts-nvl(l_plcy_cash,0)) othr_rcpts, case when a.return_type in ('08','14','20') then comp_tx_exmt_intrst else tx_exmt_intrst_m1 end exmt_intrst from micro_" & sbyear & " a left outer join control_" & sbyear & " using(scpl) left outer join dividends_" & sbyear & " b using(scpl, edit_1_2) left outer join lform_1120_" & sbyear & " c using(scpl, edit_1_2) where edit_1_2 in ('1','A')) using(scpl)"ElseIf sbyear = "c05" Then extraitems = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) where edit_1_2 in ('1','A')"Else: extraitems = ""End IfIf tabNum Mod 2 <> 0 Then$ netincm = "and net_incm_pos > 0"Else: netincm = ""End If soi_maj_cd 'h$If tabNum = "6" Or tabNum = "7" Then constraints = "")'ElseIf tabNum = "8" Or tabNum = "9" Then+' constraints = "and return_type = '01'"*ElseIf tabNum = "10" Or tabNum = "11" Then5 items10_11 = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) left outer join entity_additional_c05 b using(scpl, edit_1_2) left outer join form_1120_c05 using(scpl, edit_1_2) left outer join tax_comp_c05 using(scpl, edit_1_2) where edit_1_2 in ('1','A')"o constraints = " and (a.return_type = '05' or (frn_cd = 'Y' and a.RETURN_TYPE not in ('09', '10', '14'))) "*ElseIf tabNum = "12" Or tabNum = "13" Then@ constraints = " and a.return_type not in ('02','04','06') "*ElseIf tabNum = "14" Or tabNum = "15" Then/ constraints = " and a.return_type = '02' "*ElseIf tabNum = "16" Or tabNum = "17" Then< constraints = " and a.return_type in ('08','20','14') "*ElseIf tabNum = "24" Or tabNum = "25" ThenE constraints = " and FRN_OWNR_CD = 'Y' and FRN_OWNR_PRCNT >= 50 "End If(Establish the connection to the database L MSDAORA.19jUser ID= T ; Password= X; Data Source=SP11.WORLD9lC@q p L(n ( Z ($d'r# If arr1(1, i) = "DIV_FRN" Then5 Item = "(comp_div_frn_corp+nvl(div_cfc_85,0))" Else: Item = arr1(1, i) End If Create a Recordset&If tabNum = "10" Or tabNum = "11" Thenselect h0 indy, count(*) ct, sum(weight_f) wt, round(sum( r!*weight_f)) theitem from control_ H join form_6765_ H$ a using (scpl,edit_1_2) join micro_ H b using (scpl) where edit_1_2 in ('1','A') and ed_st in ('C','D') and b.return_type not in ('02','04','06') and a.R_TOT_ALLW_RSRCH_CR !=0 and soi_maj_cd like '3%' and  r!=0 group by rollup( h) Order by indy nulls last'tElse: SQLstring = "select " & indy_cd & " indy_cd, count(*) ct, sum(weight_f) wt, round(sum(" & Item & "*weight_f)) theitem from micro_" & sbyear & extraitems & " and round(" & Item & "*weight_f) <> 0 " & netincm & constraints & " group by rollup(soi_div_cd,case soi_maj_cd when '625' then '6215' when '626' then '622' when '521' then '520' when '522' then '520' else soi_maj_cd end) Order by " & indy_cd & " nulls first"End If.P tx |z L%v.P @ @ @@ ~ @ ~ ($d ~ @+@ P!theitem$P ~ @$@ct$P!. ~ @ +@wt$P!. ~ @ +@ PB@kdFykp ~ ( @$, @ @$,+f PB@B LB@B '@oڷAttribute VB_Name = "Module5" Option Base 1 Public myArray2, s, ac, tabNumbyearNSub a rrESV@2() Dim conn As New ADODB.Ceck1!rst Recordset   UserIdInputBox("/Schema", ID"t4 PasswE 1 lastColumnAMveSheet.3dRan,geZsunNRow 4 1$(Cells(1, 01), &, D)M"38 sˀwhat ? (cYR)@K'sA Whichyle )2,3,4D,5*R5)xe you cre@ating?ATab berA@ 'If ""c04" Then@hext toda"With2 .Pr oa5r MSDA`ORA.102( PnS0nga1 ID=u =u;栂w"; D@ Source=SP11. WORLDgOp{!0E A P&n.CursorL ocpp= adq`Cli=u qF0i 4 To#IP!iPw!a ep"0DIV_K/ d"(‰ĐϒÒ {UAS: ' Ce`R1@` WW sql%#%n,1)-dy`op(*) ctum(weight_f) wrd(spk*F)A&1^ 72"T6765-a3𨁨G?B_P heǿT 0 Then$ netincm = "and net_incm_pos > 0"Else: netincm = ""End If| case when soi_div_cd in ('42','44') then '41' when soi_div_cd in ('61','62','71','72','81') then '900' else soi_div_cd end 'h$If tabNum = "6" Or tabNum = "7" Then constraints = "")'ElseIf tabNum = "8" Or tabNum = "9" Then+' constraints = "and return_type = '01'"*ElseIf tabNum = "10" Or tabNum = "11" Then5 items10_11 = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) left outer join entity_additional_c05 b using(scpl, edit_1_2) left outer join form_1120_c05 using(scpl, edit_1_2) left outer join tax_comp_c05 using(scpl, edit_1_2) where edit_1_2 in ('1','A')"o constraints = " and (a.return_type = '05' or (frn_cd = 'Y' and a.RETURN_TYPE not in ('09', '10', '14'))) "*ElseIf tabNum = "12" Or tabNum = "13" Then@ constraints = " and a.return_type not in ('02','04','06') "*ElseIf tabNum = "14" Or tabNum = "15" Then/ constraints = " and a.return_type = '02' "*ElseIf tabNum = "16" Or tabNum = "17" Then< constraints = " and a.return_type in ('08','20','14') "*ElseIf tabNum = "24" Or tabNum = "25" ThenE constraints = " and FRN_OWNR_CD = 'Y' and FRN_OWNR_PRCNT >= 50 "End If(Establish the connection to the database L MSDAORA.19jUser ID= T ; Password= X; Data Source=SP11.WORLD9lC@q  p L(n ( Z ($d'r# If arr1(1, i) = "DIV_FRN" Then5 Item = "(comp_div_frn_corp+nvl(div_cfc_85,0))" Else: Item = arr1(1, i) End If Create a Recordset&If tabNum = "10" Or tabNum = "11" Then select nvl( h3,1) indy, count(*) ct, sum(weight_f) wt, round(sum( r!*weight_f)) theitem from control_ H join form_6765_ H$ a using (scpl,edit_1_2) join micro_ H b using (scpl) where edit_1_2 in ('1','A') and ed_st in ('C','D') and b.return_type not in ('02','04','06') and a.R_TOT_ALLW_RSRCH_CR !=0 and r_cy_qual_exp < r_gen_50prcnt and r_allw_rsrch_exp <> 0 and r_cy_qual_exp <> 0 and  r!=0 group by rollup( h) Order by indy nulls last'tElse: SQLstring = "select " & indy_cd & " indy_cd, count(*) ct, sum(weight_f) wt, round(sum(" & Item & "*weight_f)) theitem from micro_" & sbyear & extraitems & " and round(" & Item & "*weight_f) <> 0 " & netincm & constraints & " group by rollup(soi_div_cd,case soi_maj_cd when '625' then '6215' when '626' then '622' when '521' then '520' when '522' then '520' else soi_maj_cd end) Order by " & indy_cd & " nulls first"End If.P tx |z L%v.P @ @ @@ ~ @ ~ ($d ~ @+@ P!theitem$P ~ @$@ct$P!. ~ @ +@wt$P!. ~ @ +@ PB@kdFyk ~ ( @$, @ @$,+f PB@B LB@B '@o0(۷Attribute VB_Name = "Module6" Option Base 1 Public myArray2, s, ac, tabNumbyearNSub a rrESV@3() Dim conn As New ADODB.Ceck1!rst Recordset   UserIdInputBox("/Schema", ID"t4 PasswE 1 lastColumnAMveSheet.3dRan,geZsunNRow 4 1$(Cells(1, 01), &, D)M"38 sˀwhat ? (cYR)@K'sA Whichyle )2,3,4D,5*R5)xe you cre@ating?ATab berA@ 'If ""c04" Then@hextra items" @left or join (sel scpl,,omp_di@v_frn_@p +nvl(cfc_85,0))  othr_@rcpts- l_plcy_caLshB , e w& a.retutype "'08','1420') t c#tx_ex@mt_intel_m1 end from micro_" &H& " aA@trolM us@](F) @Aids b , edit_1_2lform_1120-c4Y 're& &1%A'2h'E$B5"BW"c05 !X:7iU:If Y@h 2 <> 0 ne`dcm\a Fcm_pxos @ i indW"HUHsoiBbcd#.42@.44ET'41V'cZ 6a367%!7!81900' YX 6" Or,7")sA'ntO|@f"8l9}f'!@`iA(It= '01'! $K d10, 1110`]aoMvdnyMmentity_ad@qal_'t_{taxySmp/@1('1= 50C0NQBEs8lishAsQ#ne2 toda"With2 .Pr oa5r MSDA`ORA.102( PnS0nga1 ID=u =u;栂w"; D@ Source=SP11. WORLDgOp{!0E A P&n.CursorL ocpp= adq`Cli=u qF0i 4 To#IP!iPw!a ep"0DIV_K/ d"(‰ĐϒÒ {UAS: ' Ce`R1@` WW sql%#%n,1)-dy`op(*) ctum(weight_f) wrd(spk*F)A&1^ 72"T6765-a3𨁨G?B_P heǿT 0 Then$ netincm = "and net_incm_pos > 0"Else: netincm = ""End If| case when soi_div_cd in ('42','44') then '41' when soi_div_cd in ('61','62','71','72','81') then '900' else soi_div_cd end 'h$If tabNum = "6" Or tabNum = "7" Then constraints = "")'ElseIf tabNum = "8" Or tabNum = "9" Then+' constraints = "and return_type = '01'"*ElseIf tabNum = "10" Or tabNum = "11" Then5 items10_11 = " a left outer join control_c05 using(scpl) left outer join dividends_c05 b using(scpl, edit_1_2) left outer join entity_additional_c05 b using(scpl, edit_1_2) left outer join form_1120_c05 using(scpl, edit_1_2) left outer join tax_comp_c05 using(scpl, edit_1_2) where edit_1_2 in ('1','A')"o constraints = " and (a.return_type = '05' or (frn_cd = 'Y' and a.RETURN_TYPE not in ('09', '10', '14'))) "*ElseIf tabNum = "12" Or tabNum = "13" Then@ constraints = " and a.return_type not in ('02','04','06') "*ElseIf tabNum = "14" Or tabNum = "15" Then/ constraints = " and a.return_type = '02' "*ElseIf tabNum = "16" Or tabNum = "17" Then< constraints = " and a.return_type in ('08','20','14') "*ElseIf tabNum = "24" Or tabNum = "25" ThenE constraints = " and FRN_OWNR_CD = 'Y' and FRN_OWNR_PRCNT >= 50 "End If(Establish the connection to the database L MSDAORA.19jUser ID= T ; Password= X; Data Source=SP11.WORLD9lC@q  p L(n ( Z ($d'r# If arr1(1, i) = "DIV_FRN" Then5 Item = "(comp_div_frn_corp+nvl(div_cfc_85,0))" Else: Item = arr1(1, i) End If Create a Recordset&If tabNum = "10" Or tabNum = "11" Then select nvl( h3,1) indy, count(*) ct, sum(weight_f) wt, round(sum( r!*weight_f)) theitem from control_ H join form_6765_ H$ a using (scpl,edit_1_2) join micro_ H b using (scpl) where edit_1_2 in ('1','A') and ed_st in ('C','D') and b.return_type not in ('02','04','06') and a.R_TOT_ALLW_RSRCH_CR !=0 and r_cy_qual_exp > r_gen_50prcnt and r_allw_rsrch_exp <> 0 and r_gen_50prcnt <> 0 and  r!=0 group by rollup( h) Order by indy nulls last'tElse: SQLstring = "select " & indy_cd & " indy_cd, count(*) ct, sum(weight_f) wt, round(sum(" & Item & "*weight_f)) theitem from micro_" & sbyear & extraitems & " and round(" & Item & "*weight_f) <> 0 " & netincm & constraints & " group by rollup(soi_div_cd,case soi_maj_cd when '625' then '6215' when '626' then '622' when '521' then '520' when '522' then '520' else soi_maj_cd end) Order by " & indy_cd & " nulls first"End If.P tx |z L%v.P @ @ @@ ~ @ ~ ($d ~ @+@ P!theitem$P ~ @$@ct$P!. ~ @ +@wt$P!. ~ @ +@ PB@kdFyk ~ ( @$, @ @$,+f PB@B LB@B '@o0(۷Attribute VB_Name = "Module7" Option Base 1 Public myArray2, s, ac, tabNumbyearNSub a rrESV@4() Dim conn As New ADODB.Ceck1!rst Recordset   UserIdInputBox("/Schema", ID"t4 PasswE 1 lastColumnAMveSheet.3dRan,geZsunNr Ps J< rstdole>stdole h%^*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\syst em32\e2.tlb#OLE Automation`EOffDicEOficEE2DF8D04C-5BFA-101B-BDE5EAAC42Egram Files\CommonMicrosoft Shared\OFFICE12\MSO.0DLL#M 1q Ob Li`braryKADODB>AADOB5_A75196C-D9EB-4129-B803-931327F72D5ZC/8R/SBW\@ado\ms@1 5.dll,ActiveX Data-s 2.8EK.˜ ‚ThisWorkbookG/T@isW@rkbUo 2 QHB1¦%B,!(P"B+BSheet15G#et15 &2c@OModule3CM=du3DC O݀:y!Yi 4444'.@11J11w:~ 2222Cm xI6IC055R#p6o6Uj66۰ت7777uY DataThisWorkbookThisWorkbookSheet15Sheet15Module1Module1Module2Module2Module3Module3Module4Module4Module5Module5Module6Module6Module7Module73ae9ac5TPROJECTx+SummaryInformation(DocumentSummaryInformation8CompObjrID="{7B1133EB-E042-4B99-87E1-7497C2D33635}" Document=ThisWorkbook/&H00000000 Document=Sheet15/&H00000000 Module=Module1 Module=Module2 Module=Module3 Module=Module4 Module=Module5 Module=Module6 Module=Module7 Name="VBAProject" HelpContextID="0" VersionCompatible32="393222000" CMG="25279107B1E7B5E7B5E7B5E7B5" DPB="0103B5B6B6B6B6B6" GC="DDDF694F992A9A2A9AD5" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=0, 0, 0, 0, C Sheet15=0, 0, 0, 0, C Module1=110, 145, 1436, 800, Module2=132, 174, 1458, 829, Module3=154, 203, 1480, 858, Module4=176, 232, 1502, 887, Sheet2=0, 0, 0, 0, C Sheet3=0, 0, 0, 0, C Sheet4=0, 0, 0, 0, C Module5=44, 58, 1466, 721, Module6=66, 87, 1488, 750, Module7=88, 116, 1510, 779, Z LOd[Oh+'0HPdx  exkimu00 blhodg00Microsoft Excela  *\G{000204EF-0000-0000-C000-000000000046}#4.0#9#C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL#Visual Basic For Applications*\G{00020813-0000-0000-C000-000000000046}#1.6#0#C:\Program Files\Microsoft Office\Office12\EXCEL.EXE#Microsoft Excel 12.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINDOWS\system32\stdole2.tlb#OLE Automation(*\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.4#0#C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL#Microsoft Office 12.0 Object Library*\G{2A75196C-D9EB-4129-B803-931327F72D5C}#2.8#0#C:\Program Files\Common Files\System\ado\msado15.dll#Microsoft ActiveX Data Objects 2.8 Library   Ps ThisWorkbook0153ae9ac5ThisWorkbookP%Sheet150253ae9ac5Sheet15@%Module30353ae9ac5Module3y0Module40453ae9ac54Module4.@HModule10553ae9ac5>Module1~ `wModule20653ae9ac5Module2 xxModule50753ae9ac5Module5#Module60853ae9ac5Module6بModule70953ae9ac5Module7Hx`0H@sK9uh9F俘1IKzb^