Saturday, March 4, 2017

VBA code to download stock information from yahoo finance for a selected company in a listbox,the output should consist date open ,high ,low ,close ,volume ,adj close

Option Explicit

Private Sub CommandButton1_Click()

   Dim Symbol As String
   Dim StartDate As Date
   Dim EndDate As Date
   Dim StartDay As Integer
   Dim StartMonth As Integer
   Dim StartYear As Integer
   Dim EndDay As Integer
   Dim EndMonth As Integer
   Dim EndYear As Integer
   Dim URL As String
   If ListBox1.ListIndex <> -1 Then
     Symbol = ListBox1.Text
 On Error GoTo IncorrectDates
     StartDate = TextBox1.Text
     EndDate = TextBox2.Text
On Error GoTo 0

     StartDay = Day(StartDate)
     StartMonth = Month(StartDate) - 1
     StartYear = Year(StartDate)
     EndDay = Day(EndDate)
     EndMonth = Month(EndDate) - 1
     EndYear = Year(EndDate)

     URL = "" _
     & Symbol & "&d=" & EndMonth & "&e=" & EndDay & "&f=" & EndYear _
     & "&g=d&a=" & StartMonth & "&b=" & StartDay & "&c=" _
     & StartYear & "&ignore=.csv"
     MsgBox URL
     Workbooks.Open (URL)

     Cells(1, 1).CurrentRegion.Copy
     Sheets.Add after:=Sheets(Sheets.Count)

     ActiveSheet.Name = Symbol


Application.CutCopyMode = False

     MsgBox "Select something in the list"
   End If
   Exit Sub

   MsgBox "Incorrect dates"

End Sub

Private Sub CommandButton2_Click()
'Dim cell As Range


    'For Each cell In Range(Cells(2, 1), Cells(2, 1).End(xlDown))
   '   Me.ListBox1.AddItem cell.Value

    '  Me.ListBox1.List(ListBox1.ListCount - 1, 1) = cell.Offset(0, 1).Value

    'Next cell

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Activate()


End Sub

Private Sub UserForm_Initialize()

End Sub

No comments:

Post a Comment