Malicious Office (OLE) — malware analysis report

Static analysis result for SHA-256 08370136a3c629a9…

MALICIOUS

Office (OLE)

846.5 KB Created: 2002-08-07 19:23:46 Authoring application: Microsoft Excel First seen: 2015-10-03
MD5: d961676495fbf1c8a633420a09f02ce0 SHA-1: 6d5c101c7f672f6672722c4dcbafe59afc3164f4 SHA-256: 08370136a3c629a98b2f232015d7bc079f0fa0dc4798a7d7c132683375786114
62 Risk Score

Malware Insights

MITRE ATT&CK
T1059.005 Visual Basic T1566.001 Spearphishing Attachment

This Excel document contains VBA macros that attempt to download data from external URLs, specifically stock market information. The heuristic 'OLE_VBA_EXPR_DROPPER_URL' indicates that these URLs are assembled using string expressions, suggesting an attempt to obfuscate the payload. The primary purpose appears to be downloading and displaying stock data, likely as a lure to disguise the execution of a malicious second-stage payload.

Heuristics 3

  • VBA macros detected medium 1 related finding OLE_VBA_MACROS
    Document contains VBA macro code
  • Payload URL assembled from a Chr()/Asc() string expression (3 URLs) high OLE_VBA_EXPR_DROPPER_URL
    A VBA macro builds its stage-2 download URL character by character from string literals concatenated with Chr()/Asc()/StrReverse() results — often nested (Chr(Asc(Chr(Asc("h")))) = "h") and split across the + and & operators, sometimes written out via Print #n, into a second-stage VBScript/PowerShell file. The URL is assembled at run time and never appears contiguously on disk, and there is no numeric array to brute-force, so a literal scan and the array recoverers both miss it. A bounded expression evaluator resolved it; surfaced as an IOC. Self-validating: only a valid host URL that is not already present verbatim in the macro is reported, so a benign macro cannot false-positive.
  • Embedded URL info EMBEDDED_URL
    One or more URLs were extracted from the document. The URL itself is not a detection — see the per-URL labels for which channel (macro, JS, link annotation, document body, ...) reached each URL.
    URL http://home.golden.net/~pjponzo/Yahoo-data.htm In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+AZO+BA+BAC+BAX+BBBY+BBT+BBY+BC+BCC+BCR+BDK+BDX+BEN+BF.B+BHI+BIIB+BJS+BK+BLI+BLL+BLS+BMC+BMET+BMS+BMY+BNI+BOL+BR+BRCM+BSC+BSX+BUD+C+CA+CAG+CAH+CAT+CB+CBE+CC+CCE+CCL+CCU+CD+CE+CEG+CF+CFC+CHIR+CI+CIEN+CIN+CINF+CL+CLX+CMA+CMCSA+CMI+CMS+CMVT+CNP+COF+COL+COP+COST+CPB+CPN+CPWR+CR+CSC+CSCO+CSX+CTAS+CTB+CTL+CTX+CTXS+CVG+CVS+CVX+CZN+D+DAL+DCN+DD+DDS+DE+DELL+DG+DGX+DHR+DIS+DJ+DLX+DNY+DOV+DOW+DPH+DRI+DTE+DUK+DVN+DYN+EBAY+EC+ECL+ED+EDS+EFX+EIX+EK+EMC+EMN+EMR+EOG+EOP+EP+EQR+ERTS+ESRX+ETN+ETR+EXC+F+FBF+FCX+FD+FDC+FDO+FDX+FE+FII+FISV+FITB+FLR+FNM+FO+FON+FPL+FRE+FRX+FTN+G+GAS+GCI+GD+GDT&f=sl1ej1In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=GDW+GE+GENZ+GIS+GLK+GLW+GM+GP+GPC+GPS+GR+GS+GT+GTW+GWW+HAL+HAS+HBAN+HCA+HCR+HD+HDI+HET+HIG+HLT+HMA+HNZ+HON+HOT+HPC+HPQ+HRB+HSY+HUM+IBM+IFF+IGT+INTC+INTU+IP+IPG+IR+ITT+ITW+JBL+JCI+JCP+JDSU+JHF+JNJ+JNS+JNY+JP+JPM+JWN+K+KBH+KEY+KG+KLAC+KMB+KMG+KMI+KO+KR+KRB+KRI+KSE+KSS+LEG+LEH+LIZ+LLTC+LLY+LMT+LNC+LOW+LPX+LSI+LTD+LTR+LU+LUV+LXK+MAR+MAS+MAT+MAY+MBI+MCD+MCK+MCO+MDP+MDT+MEDI+MEL+MER+MERQ+MET+MHP+MHS+MI+MIL+MKC+MMC+MMM+MNST+MO+MOLX+MON+MOT+MRK+MRO+MSFT+MTG+MU+MWD+MWV+MXIM+MYG+NAV+NBR+NCC+NCR+NE+NEM+NFB+NI+NKE+NOC+NOVL+NSC+NSM+NTAP+NTRS+NUE+NVDA+NVLS+NWL+NXTL+NYT+ODP+OMC+ONE+ORCL+OXY+PAYX+PBG+PBI+PCAR+PCG+PCL+PCS+PD+PEG+PEP+PFE+PFG+PG+PGL+PGN+PGR+PH+PHM+PKI+PLD+PLL+PMCS+PMTC+PNC+PNW+PPG+PPL+PRU+PSFT+PTV+PVN+PWER+PX+Q+QCOM+QLGC+R+RBK+RDC+RF+RHI+RIG+RJR+RKY+ROH+ROK+RSH+RTN+RX+S+SAFC+SANM+SBC+SBL&f=sl1ej1In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=SBUX+SCH+SDS+SEBL+SEE+SFA+SGP+SHW+SIAL+SLB+SLE+SLM+SLR+SNA+SNV+SO+SOTR+SPC+SPG+SPLS+SRE+STI+STJ+STT+SUN+SUNW+SVU+SWK+SWY+SYK+SYMC+SYY+T+TAP.B+TE+TEK+TER+TGT+THC+TIF+TIN+TJX+TLAB+TMK+TMO+TNB+TOY+TRB+TROW+TSG+TUP+TWX+TXN+TXT+TXU+TYC+UCL+UIS+UNH+UNM+UNP+UPC+UPS+USB+UST+UTX+UVN+VC+VFC+VIA.B+VMC+VRTS+VZ+WAG+WAT+WB+WEN+WFC+WHR+WIN+WLP+WM+WMB+WMI+WMT+WOR+WPI+WWY+WY+WYE+X+XEL+XL+XLNX+XOM+XRX+YHOO+YUM+ZION+ZMH&f=sl1ej1In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040211_C.xlsIn document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+AZO+BA+BAC+BAX+BBBY+BBT+BBY+BC+BCC+BCR+BDK+BDX+BEN+BF.B+BHI+BIIB+BJS+BK+BLI+BLL+BLS+BMC+BMET+BMS+BMY+BNI+BOL+BR+BRCM+BSC+BSX+BUD+C+CA+CAG+CAH+CAT+CB+CBE+CC+CCE+CCL+CCU+CD+CE+CEG+CF+CFC+CHIR+CI+CIEN+CIN+CINF+CL+CLX+CMA+CMCSA+CMI+CMS+CMVT+CNP+COF+COL+COP+COST+CPB+CPN+CPWR+CR+CSC+CSCO+CSX+CTAS+CTB+CTL+CTX+CTXS+CVG+CVS+CVX+CZN+D+DAL+DCN+DD+DDS+DE+DELL+DG+DGX+DHR+DIS+DJ+DLX+DNY+DOV+DOW+DPH+DRI+DTE+DUK+DVN+DYN+EBAY+EC+ECL+ED+EDS+EFX+EIX+EK+EMC+EMN+EMR+EOG+EOP+EP+EQR+ERTS+ESRX+ETN+ETR+EXC+F+FBF+FCX+FD+FDC+FDO+FDX+FE+FII+FISV+FITB+FLR+FNM+FO+FON+FPL+FRE+FRX+FTN+G+GAS+GCI+GD+GDT&f=sl1ej1KIn document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=GDW+GE+GENZ+GIS+GLK+GLW+GM+GP+GPC+GPS+GR+GS+GT+GTW+GWW+HAL+HAS+HBAN+HCA+HCR+HD+HDI+HET+HIG+HLT+HMA+HNZ+HON+HOT+HPC+HPQ+HRB+HSY+HUM+IBM+IFF+IGT+INTC+INTU+IP+IPG+IR+ITT+ITW+JBL+JCI+JCP+JDSU+JHF+JNJ+JNS+JNY+JP+JPM+JWN+K+KBH+KEY+KG+KLAC+KMB+KMG+KMI+KO+KR+KRB+KRI+KSE+KSS+LEG+LEH+LIZ+LLTC+LLY+LMT+LNC+LOW+LPX+LSI+LTD+LTR+LU+LUV+LXK+MAR+MAS+MAT+MAY+MBI+MCD+MCK+MCO+MDP+MDT+MEDI+MEL+MER+MERQ+MET+MHP+MHS+MI+MIL+MKC+MMC+MMM+MNST+MO+MOLX+MON+MOT+MRK+MRO+MSFT+MTG+MU+MWD+MWV+MXIM+MYG+NAV+NBR+NCC+NCR+NE+NEM+NFB+NI+NKE+NOC+NOVL+NSC+NSM+NTAP+NTRS+NUE+NVDA+NVLS+NWL+NXTL+NYT+ODP+OMC+ONE+ORCL+OXY+PAYX+PBG+PBI+PCAR+PCG+PCL+PCS+PD+PEG+PEP+PFE+PFG+PG+PGL+PGN+PGR+PH+PHM+PKI+PLD+PLL+PMCS+PMTC+PNC+PNW+PPG+PPL+PRU+PSFT+PTV+PVN+PWER+PX+Q+QCOM+QLGC+R+RBK+RDC+RF+RHI+RIG+RJR+RKY+ROH+ROK+RSH+RTN+RX+S+SAFC+SANM+SBC+SBL&f=sl1ej1�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040211_C.xls�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040206_C.xls�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040203_C.xls�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20031205_C.xls�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040130_C.xls�In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20040204_C.xls�In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+AZO+BA+BAC+BAX+BBBY+BBT+BBY+BC+BCC+BCR+BDK+BDX+BEN+BF.B+BHI+BIIB+BJS+BK+BLI+BLL+BLS+BMC+BMET+BMS+BMY+BNI+BOL+BR+BRCM+BSC+BSX+BUD+C+CA+CAG+CAH+CAT+CB+CBE+CC+CCE+CCL+CCU+CD+CE+CEG+CF+CFC+CHIR+CI+CIEN+CIN+CINF+CL+CLX+CMA+CMCSA+CMI+CMS+CMVT+CNP+COF+COL+COP+COST+CPB+CPN+CPWR+CR+CSC+CSCO+CSX+CTAS+CTB+CTL+CTX+CTXS+CVG+CVS+CVX+CZN+D+DAL+DCN+DD+DDS+DE+DELL+DG+DGX+DHR+DIS+DJ+DLX+DNY+DOV+DOW+DPH+DRI+DTE+DUK+DVN+DYN+EBAY+EC+ECL+ED+EDS+EFX+EIX+EK+EMC+EMN+EMR+EOG+EOP+EP+EQR+ERTS+ESRX+ETN+ETR+EXC+F+FBF+FCX+FD+FDC+FDO+FDX+FE+FII+FISV+FITB+FLR+FNM+FO+FON+FPL+FRE+FRX+FTN+G+GAS+GCI+GD+GDT&f=sl1ej1�In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=SBUX+SCH+SDS+SEBL+SEE+SFA+SGP+SHW+SIAL+SLB+SLE+SLM+SLR+SNA+SNV+SO+SOTR+SPC+SPG+SPLS+SRE+STI+STJ+STT+SUN+SUNW+SVU+SWK+SWY+SYK+SYMC+SYY+T+TAP.B+TE+TEK+TER+TGT+THC+TIF+TIN+TJX+TLAB+TMK+TMO+TNB+TOY+TRB+TROW+TSG+TUP+TWX+TXN+TXT+TXU+TYC+UCL+UIS+UNH+UNM+UNP+UPC+UPS+USB+UST+UTX+UVN+VC+VFC+VIA.B+VMC+VRTS+VZ+WAG+WAT+WB+WEN+WFC+WHR+WIN+WLP+WM+WMB+WMI+WMT+WOR+WPI+WWY+WY+WYE+X+XEL+XL+XLNX+XOM+XRX+YHOO+YUM+ZION+ZMH&f=sl1ej1�In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=SBUX+SCH+SDS+SEBL+SEE+SFA+SGP+SHW+SIAL+SLB+SLE+SLM+SLR+SNA+SNV+SO+SOTR+SPC+SPG+SPLS+SRE+STI+STJ+STT+SUN+SUNW+SVU+SWK+SWY+SYK+SYMC+SYY+T+TAP.B+TE+TEK+TER+TGT+THC+TIF+TIN+TJX+TLAB+TMK+TMO+TNB+TOY+TRB+TROW+TSG+TUP+TWX+TX�In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=GDW+GE+GENZ+GIS+GLK+GLW+GM+GP+GPC+GPS+GR+GS+GT+GTW+GWW+HAL+HAS+HBAN+HCA+HCR+HD+HDI+HET+HIG+HLT+HMA+HNZ+HON+HOT+HPC+HPQ+HRB+HSY+HUM+IBM+IFF+IGT+INTC+INTU+IP+IPG+IR+ITT+ITW+JBL+JCI+JCP+JDSU+JHF+JNJ+JNS+JNY+JP+JPM+JWN+K+�In document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+AZ�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=SBUX+SCH+SDS+SEBL+SEE+SFA+SGP+SHW+SIAL+SLB+SLE+SLM+SLR+SNA+SNV+SO+SOTR+SPC+SPG+SPLS+SRE+STI+STJ+STT+SUN+SUNW+SVU+SWK+SWY+SYK+SYMC+SYY+T+TAP.B+TE+TEK+TER+TGT+THC+TIF+TIN+TJX+TLAB+TMK+TMO+TNB+TOY+TRB+TROW+TSG+TUP+TWX+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=GDW+GE+GENZ+GIS+GLK+GLW+GM+GP+GPC+GPS+GR+GS+GT+GTW+GWW+HAL+HAS+HBAN+HCA+HCR+HD+HDI+HET+HIG+HLT+HMA+HNZ+HON+HOT+HPC+HPQ+HRB+HSY+HUM+IBM+IFF+IGT+INTC+INTU+IP+IPG+IR+ITT+ITW+JBL+JCI+JCP+JDSU+JHF+JNJ+JNS+JNY+JP+JPM+JWN+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMC����In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=MMM+ABT+ACE+ADCT+ADBE+AMD+AES+AET+AFL+A+APD+ACV+ABS+AA+AYE+ATI+AGN+AW+ALL+AT+ALTR+MO+ABK+AHC+AEE+AEP+AXP+AM+AIG+APCC+ASD+ABC+AMGN+ASO+APC+ADI+ANDW+BUD+ATH+AOC+APA+AIV+APOL+AAPL+ABI+AMAT+AMCC+ADM+ASH+T+AWE+ADSK+ADP+A�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=FCX+GCI+GPS+GTW+GD+GE+GIS+GM+GPC+GENZ+GP+G+GDW+GS+GR+GT+GWW+GLK+GDT+HAL+HDI+HET+HIG+HAS+HCA+HMA+HNZ+HPC+HSY+HPQ+HLT+HD+HON+HUM+HBAN+ITW+RX+IR+INTC+IBM+IFF+IGT+IP+IPG+INTU+ITT+JPM+JBL+JNS+JDSU+JP+JHF+JNJ+JCI+JNY+KBH+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=SFA+SEE+S+SRE+SHW+SEBL+SIAL+SPG+SLM+SNA+SLR+SO+SOTR+LUV+FON+PCS+STJ+SPC+SWK+SPLS+SBUX+HOT+STT+SYK+SUNW+SDS+SUN+STI+SVU+SYMC+SBL+SNV+SYY+TROW+TGT+TE+TEK+TLAB+TIN+THC+TER+TXN+TXT+TMO+TNB+TIF+TWX+TJX+TMK+TOY+RIG+TAP.B+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=A+AA+AAPL+ABC+ABI+ABK+ABS+ABT+ACE+ACV+ADBE+ADCT+ADI+ADM+ADP+ADSK+AEE+AEP+AES+AET+AFL+AGN+AHC+AIG+AIV+ALL+ALTR+AM+AMAT+AMCC+AMD+AMGN+AN+ANDW+AOC+APA+APC+APCC+APD+APOL+ASD+ASH+ASO+AT+ATH+ATI+AV+AVP+AVY+AW+AWE+AXP+AYE+�����In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=SBUX+SCH+SDS+SEBL+SEE+SFA+SGP+SHW+SIAL+SLB+SLE+SLM+SLR+SNA+SNV+SO+SOTR+SPC+SPG+SPLS+SRE+STI+STJ+STT+SUN+SUNW+SVU+SWK+SWY+SYK+SYMC+SYY+T+TAP.B+TE+TEK+TER+TGT+THC+TIF+�In document text (OLE body)
    • http://finance.yahoo.com/d/quotes.csv?s=In document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500_20031205_C.xlsIn document text (OLE body)
    • http://quote.yahoo.com/d/quotes.csv?s=Referenced by macro
    • http://www2.standardandpoors.com/spf/xls/index/500_Referenced by macro
    • http://quote.yahoo.com/d/quotes.csv?s=iIn document text (OLE body)
    • http://www2.standardandpoors.com/spf/xls/index/500__C.xlsReferenced by macro
    • http://quote.yahoo.com/d/quotes.csv?s=+Referenced by macro
    • http://quote.yahoo.com/d/quotes.csv?s=&f=sl1ej1Referenced by macro

Extracted artifacts 1

Files carved from inside the sample during analysis.

FilenameKindSourceSize
macros.bas vba-macro oletools.olevba.extract_macros (decoded VBA source) 7034 bytes
SHA-256: fc5943409a22a4ce34d94defb869a8fc985cf4280a7f1ed0421824a574640b55
Preview script
First 1,000 lines of the extracted script
Attribute VB_Name = "Module1"

Attribute VB_Name = "ThisWorkbook"
Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "Sheet1"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "Module2"

Attribute VB_Name = "Sheet2"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "Module3"
Sub GetData()

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer
    Dim nQuery As Name
    
'    Application.ScreenUpdating = False
'    Application.DisplayAlerts = False
'    Application.Calculation = xlCalculationManual
    
    Set DataSheet = ActiveSheet
  
Range("B7").CurrentRegion.ClearContents
Range("A1:A3").Clear
QueryQuote:
qurl = Range("D1")
Range("A1") = "Getting First 200 stocks"
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
qurl = Range("D2")
Range("A2") = "Getting Next 200 stocks"
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B207"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
qurl = Range("D3")
Range("A3") = "Getting Last 100 stocks"
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B407"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

            Range("B7").CurrentRegion.TextToColumns Destination:=Range("B7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False


'    Columns("B:B").ColumnWidth = 12
'    Rows("7:24").Select
'    Selection.RowHeight = 9.5
    
    Sheets("Data").Select
    Range("B7:E206").Select
    Selection.Copy
    Sheets("PE Ratios").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Data").Select
        Range("B207:E406").Select
    Selection.Copy
    Sheets("PE Ratios").Select
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Sheets("Data").Select
    Range("B407:E506").Select
    Selection.Copy
    Sheets("PE Ratios").Select
    Range("S2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Data").Select
    
Range("A1:A3").Clear
Range("A1") = "Finished transferring"
Range("A2") = "to PE sheet and"
Range("A3") = "Sorting by Mkt Cap"


    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
        End If
    Next nQuery
    

    Range("B7:E506").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Range("B7:H506").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G7"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("Q12") = Range("O12")
Range("A1").Select
End With


End Sub




Attribute VB_Name = "Module4"

Attribute VB_Name = "Module5"
Sub GetSP()

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String, URL As String
    Dim i As Integer
    Dim nQuery As Name
    
'    Application.ScreenUpdating = False
'    Application.DisplayAlerts = False
'    Application.Calculation = xlCalculationManual
    
Set DataSheet = ActiveSheet
  
Range("A1:A3").Select
Selection.Clear
Range("B7").CurrentRegion.ClearContents
QueryQuote:
qurl = "http://www2.standardandpoors.com/spf/xls/index/500_" & Range("C5") & "_C.xls"
Range("A4") = qurl

             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With


    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
        End If
    Next nQuery
    
End With
    Range("B8:G507").Select
    Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


URL = "http://quote.yahoo.com/d/quotes.csv?s="
For i = 1 To 199
    URL = URL + Cells(7 + i, 9) + "+"
Next i
URL = URL + Cells(207, 9) + "&f=sl1ej1"
Range("A1") = URL

URL = "http://quote.yahoo.com/d/quotes.csv?s="
For i = 1 To 199
    URL = URL + Cells(207 + i, 9) + "+"
Next i
URL = URL + Cells(407, 9) + "&f=sl1ej1"
Range("A2") = URL

URL = "http://quote.yahoo.com/d/quotes.csv?s="
For i = 1 To 99
    URL = URL + Cells(407 + i, 9) + "+"
Next i
URL = URL + Cells(507, 9) + "&f=sl1ej1"
Range("A3") = URL

    Range("B8:G507").Select
    Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
Range("E5") = Range("D5")
Range("A5").Select
Selection.Clear
End Sub




Attribute VB_Name = "Sheet3"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "Module6"

Attribute VB_Name = "Sheet4"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Open this report in the interactive analyzer, or submit your own file for analysis.