XOS>> Stock_Analysis_with_VBA>> 返回
项目作者： ibaloyan

项目描述 ：
Create VBA script for Stock Data Analysis, loop through Stock Market Data and return Yearly Stock Performance Results.
高级语言： Visual Basic
项目地址: git://github.com/ibaloyan/Stock_Analysis_with_VBA.git
创建时间： 2018-12-25T04:59:48Z
项目社区：https://github.com/ibaloyan/Stock_Analysis_with_VBA
开源协议：
下载


Stock_Analysis_with_VBA

Create VBA script for Stock Data Analysis, loop through Stock Market Data and return Yearly Stock Performance Results.

Initial Stock Market Data:

Stock Data

The Objective of the Project:

The objective of this project is to create a VBA script that provides easy analysis of yearly stock performance. The worksheets are organized by year and contain the starting, final, highest, and lowest value of each stock for each day of the year. The VBA script will give the yearly increase/decrease in stock value for each stock, the percentage change over the year, and the total volume for the year. In addition the stock will give the greatest increase, decrease, and total volume.

The data has been sorted such that the stocks are grouped together by ticker value in alphabetical order
The data is sorted by date such that the dates are in order with the first date for a given stock is the begining of the year and the last value for a give stock is the last date of the year traded.
Non-zero stock value is not assumed. The data in the spreadsheets represents the complete data with no missing values.

  • Extract Ticker Symbols:
    The unique ticker symbols are extracted and inserted into a column.

  • Extract Initial and Final Values for Stock Price:
    The initial and final values for the stock price. As non-zero values are not assumed, the first non-zero value is taken to avoid division by zero.

  • Calculate Yearly Change, Percentage Change, Total Volume:
    The values are calculated for yearly change in stock values, yearly percentage change, and yearly total volume. Values are displayed in spreadsheet next to corresponding ticker value.
    Yearly Change = Final Value - Initial (non-zero) Value
    Percentage Change = Yearly Change / Initial (non-zero) Value
    Total Volume = Sum(Daily Volume)

  • Determine Max and Min values:
    The “Greatest % Increase”, “Greatest % Decrease”, and “Greatest Yearly Volume” are determined from the values calculated in the last step and displayed in the chart.

  • Provide conditional formatting that will highlight positive change in green and negative change in red.

  • VBA script is run once and produces all appropriate adjustments on every worksheet.

Yearly Stock Performance Results ( 2014-2016 )

hard_solution

VBA code:

‘ Hard Solution VB code - Inna Baloyan May2018 Bootcamp
‘ Going through all worksheets

Sub WorksheetsLoop()

  1.     ' Set CurrentWs as a worksheet object variable.
  2.     Dim CurrentWs As Worksheet
  3.     Dim Need_Summary_Table_Header As Boolean
  4.     Dim COMMAND_SPREADSHEET As Boolean
  6.     Need_Summary_Table_Header = False       'Set Header flag
  7.     COMMAND_SPREADSHEET = True              'Hard part flag
  9.     ' Loop through all of the worksheets in the active workbook.
  10.     For Each CurrentWs In Worksheets
  12.         ' Set initial variable for holding the ticker name
  13.         Dim Ticker_Name As String
  14.         Ticker_Name = " "
  16.         ' Set an initial variable for holding the total per ticker name
  17.         Dim Total_Ticker_Volume As Double
  18.         Total_Ticker_Volume = 0
  20.         ' Set new variables for Moderate Solution Part
  21.         Dim Open_Price As Double
  22.         Open_Price = 0
  23.         Dim Close_Price As Double
  24.         Close_Price = 0
  25.         Dim Delta_Price As Double
  26.         Delta_Price = 0
  27.         Dim Delta_Percent As Double
  28.         Delta_Percent = 0
  29.         ' Set new variables for Hard Solution Part
  30.         Dim MAX_TICKER_NAME As String
  31.         MAX_TICKER_NAME = " "
  32.         Dim MIN_TICKER_NAME As String
  33.         MIN_TICKER_NAME = " "
  34.         Dim MAX_PERCENT As Double
  35.         MAX_PERCENT = 0
  36.         Dim MIN_PERCENT As Double
  37.         MIN_PERCENT = 0
  38.         Dim MAX_VOLUME_TICKER As String
  39.         MAX_VOLUME_TICKER = " "
  40.         Dim MAX_VOLUME As Double
  41.         MAX_VOLUME = 0
  42.         '----------------------------------------------------------------
  44.         ' Keep track of the location for each ticker name
  45.         ' in the summary table for the current worksheet
  46.         Dim Summary_Table_Row As Long
  47.         Summary_Table_Row = 2
  49.         ' Set initial row count for the current worksheet
  50.         Dim Lastrow As Long
  51.         Dim i As Long
  53.         Lastrow = CurrentWs.Cells(Rows.Count, 1).End(xlUp).Row
  55.         ' For all worksheet except the first one, the Results
  56.         If Need_Summary_Table_Header Then
  57.             ' Set Titles for the Summary Table for current worksheet
  58.             CurrentWs.Range("I1").Value = "Ticker"
  59.             CurrentWs.Range("J1").Value = "Yearly Change"
  60.             CurrentWs.Range("K1").Value = "Percent Change"
  61.             CurrentWs.Range("L1").Value = "Total Stock Volume"
  62.             ' Set Additional Titles for new Summary Table on the right for current worksheet
  63.             CurrentWs.Range("O2").Value = "Greatest % Increase"
  64.             CurrentWs.Range("O3").Value = "Greatest % Decrease"
  65.             CurrentWs.Range("O4").Value = "Greatest Total Volume"
  66.             CurrentWs.Range("P1").Value = "Ticker"
  67.             CurrentWs.Range("Q1").Value = "Value"
  68.         Else
  69.             'This is the first, resulting worksheet, reset flag for the rest of worksheets
  70.             Need_Summary_Table_Header = True
  71.         End If
  73.         ' Set initial value of Open Price for the first Ticker of CurrentWs,
  74.         ' The rest ticker's open price will be initialized within the for loop below
  75.         Open_Price = CurrentWs.Cells(2, 3).Value
  77.         ' Loop from the beginning of the current worksheet(Row2) till its last row
  78.         For i = 2 To Lastrow
  81.             ' Check if we are still within the same ticker name,
  82.             ' if not - write results to summary table
  83.             If CurrentWs.Cells(i + 1, 1).Value <> CurrentWs.Cells(i, 1).Value Then
  85.                 ' Set the ticker name, we are ready to insert this ticker name data
  86.                 Ticker_Name = CurrentWs.Cells(i, 1).Value
  88.                 ' Calculate Delta_Price and Delta_Percent
  89.                 Close_Price = CurrentWs.Cells(i, 6).Value
  90.                 Delta_Price = Close_Price - Open_Price
  91.                 ' Check Division by 0 condition
  92.                 If Open_Price <> 0 Then
  93.                     Delta_Percent = (Delta_Price / Open_Price) * 100
  94.                 Else
  95.                     ' Unlikely, but it needs to be checked to avoid program crushing
  96.                     MsgBox ("For " & Ticker_Name & ", Row " & CStr(i) & ": Open Price =" & Open_Price & ". Fix <open> field manually and save the spreadsheet.")
  97.                 End If
  99.                 ' Add to the Ticker name total volume
  100.                 Total_Ticker_Volume = Total_Ticker_Volume + CurrentWs.Cells(i, 7).Value
  103.                 ' Print the Ticker Name in the Summary Table, Column I
  104.                 CurrentWs.Range("I" & Summary_Table_Row).Value = Ticker_Name
  105.                 ' Print the Ticker Name in the Summary Table, Column I
  106.                 CurrentWs.Range("J" & Summary_Table_Row).Value = Delta_Price
  107.                 ' Fill "Yearly Change", i.e. Delta_Price with Green and Red colors
  108.                 If (Delta_Price > 0) Then
  109.                     'Fill column with GREEN color - good
  110.                     CurrentWs.Range("J" & Summary_Table_Row).Interior.ColorIndex = 4
  111.                 ElseIf (Delta_Price <= 0) Then
  112.                     'Fill column with RED color - bad
  113.                     CurrentWs.Range("J" & Summary_Table_Row).Interior.ColorIndex = 3
  114.                 End If
  116.                  ' Print the Ticker Name in the Summary Table, Column I
  117.                 CurrentWs.Range("K" & Summary_Table_Row).Value = (CStr(Delta_Percent) & "%")
  118.                 ' Print the Ticker Name in the Summary Table, Column J
  119.                 CurrentWs.Range("L" & Summary_Table_Row).Value = Total_Ticker_Volume
  121.                 ' Add 1 to the summary table row count
  122.                 Summary_Table_Row = Summary_Table_Row + 1
  123.                 ' Reset Delta_rice and Delta_Percent holders, as we will be working with new Ticker
  124.                 Delta_Price = 0
  125.                 ' Hard part,do this in the beginning of the for loop Delta_Percent = 0
  126.                 Close_Price = 0
  127.                 ' Capture next Ticker's Open_Price
  128.                 Open_Price = CurrentWs.Cells(i + 1, 3).Value
  131.                 ' Hard part : Populate new Summary table on the right for the current spreadsheet HERE
  132.                 ' Keep track of all extra hard counters and do calculations within the current spreadsheet
  133.                 If (Delta_Percent > MAX_PERCENT) Then
  134.                     MAX_PERCENT = Delta_Percent
  135.                     MAX_TICKER_NAME = Ticker_Name
  136.                 ElseIf (Delta_Percent < MIN_PERCENT) Then
  137.                     MIN_PERCENT = Delta_Percent
  138.                     MIN_TICKER_NAME = Ticker_Name
  139.                 End If
  141.                 If (Total_Ticker_Volume > MAX_VOLUME) Then
  142.                     MAX_VOLUME = Total_Ticker_Volume
  143.                     MAX_VOLUME_TICKER = Ticker_Name
  144.                 End If
  146.                 ' Hard part adjustments to resetting counters
  147.                 Delta_Percent = 0
  148.                 Total_Ticker_Volume = 0
  151.             'Else - If the cell immediately following a row is still the same ticker name,
  152.             'just add to Totl Ticker Volume
  153.             Else
  154.                 ' Encrease the Total Ticker Volume
  155.                 Total_Ticker_Volume = Total_Ticker_Volume + CurrentWs.Cells(i, 7).Value
  156.             End If
  157.             ' For debugging MsgBox (CurrentWs.Rows(i).Cells(2, 1))
  159.         Next i
  161.             ' For hard solution part:
  162.             ' Check if it is not the first spreadsheet
  163.             ' Record all new counts to the new summary table on the right of the current spreadsheet
  164.             If Not COMMAND_SPREADSHEET Then
  166.                 CurrentWs.Range("Q2").Value = (CStr(MAX_PERCENT) & "%")
  167.                 CurrentWs.Range("Q3").Value = (CStr(MIN_PERCENT) & "%")
  168.                 CurrentWs.Range("P2").Value = MAX_TICKER_NAME
  169.                 CurrentWs.Range("P3").Value = MIN_TICKER_NAME
  170.                 CurrentWs.Range("Q4").Value = MAX_VOLUME
  171.                 CurrentWs.Range("P4").Value = MAX_VOLUME_TICKER
  173.             Else
  174.                 COMMAND_SPREADSHEET = False
  175.             End If
  177.      Next CurrentWs

End Sub