market_analysis.vbs.rtf

{\rtf1\ansi\ansicpg1252\cocoartf2580
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\margl1440\margr1440\vieww11520\viewh8400\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 Sub market_analytics()\
\
For Each ws In Worksheets\
\
' State all variables--\
\
' set initial variable for brand name\
Dim ticker_name As String\
\
' set variable for tickercount for openbell\
Dim tickercount As Integer\
tickercount = 0\
\
'set varible to analyze date values\
Dim Day As Integer\
\
' set variable for openbell in column C, set to zero\
Dim open_bell As Double\
open_bell = 0\
\
' set variable for closing bell in column G\
Dim closing_bell As Double\
closing_bell = 0\
\
' set variable for dialy volume in column H, to be added to column L\
Dim daily_volume As Long\
daily_volume = 0\
\
' set variable for yearly change, to go in column J\
Dim yearly_change As Double\
yearly_change = 0\
\
' set variable of % change in column K\
Dim percent_change As Double\
percent_change = 0\
\
'set variable for total stock volume\
Dim total_volume As LongLong\
totaly_volume = 0\
\
' keep track of the location of each individual stock in the ticker table\
Dim ticker_row As Integer\
ticker_row = 2\
\
' Column Creation\
ws.Cells(1, 9).Value = "Ticker"\
ws.Cells(1, 10).Value = "Yearly Change"\
ws.Cells(1, 11).Value = "Percent Change"\
ws.Cells(1, 12).Value = "Total Stock Volume"\
ws.Cells(1, 16).Value = "Ticker"\
ws.Cells(1, 17).Value = "Value"\
\
' set row 15:\
' greatest increase declaration\
ws.Cells(2, 15).Value = "Greatest % Increase"\
' greatest decrease declaration\
ws.Cells(3, 15).Value = "Greatest % Decrease"\
' greatest total volume, to be calculated\
ws.Cells(4, 15).Value = "Greatest Total Volume"\
\
' set variables for calculations to be done that correspond to row 15 (remember to set at row 17)\
Dim greatest_increase As Double\
Dim greatest_decrease As Double\
Dim greatest_volume As LongLong\
\
' set variables for capturing ticker symbols for greatest values above\
Dim great_inc_tick As String\
Dim great_dec_tick As String\
Dim great_vol_tick As String\
\
' last row\
Dim lastrow As Long\
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row\
\
Dim new_rows As Long\
\
' we did this in my tutorial but I couldn't get it to work \'bf?:\
' Dim RowCount As Long\
' RowCount = Cells(Rows.Count, "A").End(x1up).Row\
\
\
    ' Now loop through all stock days for all stocks\
    For i = 2 To lastrow\
    \
        ' If the stock name is different...\
        If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then\
            \
            ' set the stock name\
            ticker_name = ws.Cells(i, 1).Value\
        \
            ' capture closing bell value\
            closingbell = ws.Cells(i, 6).Value\
        \
            'calculate yearly change\
            yearly_change = openbell - closingbell\
        \
            'calculate percentage change\
            percent_change = yearly_change / openbell\
        \
            ' add to total volume\
            total_volume = total_volume + ws.Cells(i, 7).Value\
        \
            'print ticker name\
            ws.Range("I" & ticker_row).Value = ticker_name\
        \
            ' print yearly change\
            ws.Range("J" & ticker_row).Value = yearly_change\
            \
            'print percent change\
            ws.Range("K" & ticker_row).Value = percent_change\
                \
            'print total stock volume\
            ws.Range("L" & ticker_row).Value = total_volume\
            \
            'recapture all greatest changes\
\
            \
            ' add one to ticker row\
            ticker_row = ticker_row + 1\
      \
        ' reset all calculated totals\
            openbell = 0\
            closingbell = 0\
            yearly_change = 0\
            percent_change = 0\
            total_stock = 0\
            tickercount = 0\
\
    ' if ticker symbol is the same, add stock volume to total\
    \
        Else\
            tickercount = tickercount + 1\
                If tickercount = 1 Then\
                    openbell = ws.Cells(i, 3).Value\
            End If\
            total_volume = total_volume + ws.Cells(i, 7).Value\
       End If\
    \
        \
    Next i\
\
    For i = 2 To ticker_row\
    \
        ' use format conditioning for color\
        If ws.Cells(i, 10) <= 0 Then\
            ws.Cells(i, 10).Interior.ColorIndex = 3\
        Else\
            ws.Cells(i, 10).Interior.ColorIndex = 4\
        End If\
        \
    \
    Next i\
        \
    For i = 2 To ticker_row\
'conditionally format percent_change,\
' conditionally format currency for yearly change\
    \
        If ws.Cells(i, 11).Value = WorksheetFunction.Max(Range("K2:K" & ticker_row)) Then\
            ws.Cells(2, 17).Value = ws.Cells(i, 11).Value\
            ws.Cells(2, 16).Value = ws.Cells(i, 9).Value\
                       \
        ElseIf ws.Cells(i, 11).Value = WorksheetFunction.Min(Range("K2:K" & ticker_row)) Then\
            ws.Cells(3, 16).Value = ws.Cells(i, 9).Value\
            ws.Cells(3, 17).Value = ws.Cells(i, 11).Value\
                    \
        End If\
        \
        If ws.Cells(i, 12).Value = WorksheetFunction.Max(Range("L2:L" & ticker_row)) Then\
            ws.Cells(4, 17) = ws.Cells(i, 12).Value\
            ws.Cells(4, 16) = ws.Cells(i, 9).Value\
            \
        End If\
    ws.Cells(3, 17).NumberFormat = "0.00%"\
    ws.Cells(2, 17).NumberFormat = "0.00%"\
    Next i\
    \
    ws.Range("K2:K" & ticker_row).NumberFormat = "0.00%"\
    ws.Range("J2:J" & ticker_row).NumberFormat = "$#, ##0.00"\
\
    \
      \
Next ws\
\
End Sub}