VBA_Code.rtf

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

\f0\fs24 \cf0 Sub Ticker_count()\
\
    Dim ws As Worksheet\
\
    For Each ws In ThisWorkbook.Worksheets\
    \
        Dim Worksheetname As String\
        Dim Lastrow As Double\
        Dim Tickername As String\
        Dim open_price As Double\
        Dim close_price As Double\
        Dim Volume As Integer\
        Dim Summary_Table_Row As Long\
        \
       ws.Activate\
       \
             \
        ws.Cells(1, 9).Value = "Tickername"\
        ws.Cells(1, 10).Value = "Yearly change"\
        ws.Cells(1, 11).Value = "Percent Change"\
        ws.Cells(1, 12).Value = "Total Stock Vol"\
        ws.Cells(1, 16).Value = "Value"\
        ws.Cells(2, 16).Value = "Greatest % Increase"\
        ws.Cells(3, 16).Value = "Greatest % Decrease"\
        ws.Cells(4, 16).Value = "Greatest Total Volume"\
        ws.Cells(1, 17).Value = "Ticker"\
        \
        'Last row info\
        Lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row\
        'Worksheet name\
        Worksheetname = ws.Name\
        'MsgBox (Worksheetname)\
        \
        SummaryTableRow = 2\
        Yearly_change = 0\
        Percent_change = 0\
        TotalVolume = 0\
        \
         For Row_counter = 2 To Lastrow\
    \
            If ws.Cells(Row_counter + 1, 1).Value <> ws.Cells(Row_counter, 1).Value Then\
            \
                Yearly_change = Yearly_change + (Cells(Row_counter, 6).Value - Cells(Row_counter, 3).Value)\
                Percent_change = Percent_change + ((Cells(Row_counter, 6).Value - Cells(Row_counter, 3).Value) / Cells(Row_counter, 3).Value) * 100\
                TotalVolume = TotalVolume + Cells(Row_counter, 7)\
                Tickername = ws.Cells(Row_counter, 1).Value\
               \
                ws.Range("I" & SummaryTableRow).Value = Tickername\
                ws.Range("J" & SummaryTableRow).Value = Yearly_change\
                ws.Range("L" & SummaryTableRow).Value = TotalVolume\
                ws.Range("K" & SummaryTableRow).Value = Percent_change\
                SummaryTableRow = SummaryTableRow + 1\
            \
    \
                Yearly_change = 0\
                TotalVolume = 0\
                Percent_change = 0\
         Else\
                Yearly_change = Yearly_change + (Cells(Row_counter, 6).Value - Cells(Row_counter, 3).Value)\
                TotalVolume = TotalVolume + Cells(Row_counter, 7)\
                Percent_change = Percent_change + ((Cells(Row_counter, 6).Value - Cells(Row_counter, 3).Value) / Cells(Row_counter, 3).Value) * 100\
                \
        End If\
\
            If ws.Cells(Row_counter, 10).Value < 0 Then\
                ws.Cells(Row_counter, 10).Interior.ColorIndex = 3\
            Else:\
                ws.Cells(Row_counter, 10).Interior.ColorIndex = 4\
            \
            End If\
\
        \
        Next Row_counter\
\
        \
                        \
            Lastrow = ws.Cells(Rows.Count, 11).End(xlUp).Row\
            \
            Dim tName As String\
            \
            Max_percentvalue = Cells(2, 11).Value\
            Min_percentvalue = Cells(2, 11).Value\
            Greatest_TotalValue = Cells(2, 12).Value\
            tName = Cells(2, 9).Value\
           \
            For Rownum = 2 To Lastrow\
                 \
                    If ws.Cells(Rownum, 11).Value > Max_value Then\
                        Max_percentvalue = ws.Cells(Rownum, 11).Value\
                        tName_increase = Cells(Rownum, 9).Value\
                        \
                    End If\
                    \
                    If ws.Cells(Rownum, 11).Value < Min_value Then\
                        Min_percentvalue = ws.Cells(Rownum, 11).Value\
                        tName_decrease = Cells(Rownum, 9).Value\
                    \
                    End If\
                        \
                    If ws.Cells(Rownum, 12).Value > Greatest_TotalValue Then\
                        Greatest_TotalValue = ws.Cells(Rownum, 12).Value\
                        tName_GreatestTotalVol = Cells(Rownum, 9).Value\
                        \
                    End If\
                    \
            Next Rownum\
            \
            ws.Cells(2, 18).Value = Max_percentvalue\
            ws.Cells(2, 17).Value = tName_increase\
            ws.Cells(3, 18).Value = Min_percentvalue\
            ws.Cells(3, 17).Value = tName_decrease\
            ws.Cells(4, 17).Value = tName_GreatestTotalVol\
            ws.Cells(4, 18).Value = Greatest_TotalValue\
            \
    Next ws\
    \
 \
    \
    End Sub\
}