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 = "http://real-chart.finance.yahoo.com/table.csv?s=" _
     & 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
     
     'Workbooks("Assets.xlsm").Activate
     
     Sheets.Add after:=Sheets(Sheets.Count)

     ActiveSheet.Name = Symbol

     ActiveSheet.Paste

     Columns(1).AutoFit
Application.CutCopyMode = False

   Else
   
     MsgBox "Select something in the list"
     
   End If
   
   Exit Sub
   
IncorrectDates:

   MsgBox "Incorrect dates"


End Sub

Private Sub CommandButton2_Click()
'Dim cell As Range

    'Sheets("Companies").Select

    '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

Populate listbox from a range and filter listbox data with two comboboxes and show the filtered data in the listbox,vba teacher sourav,kolkata 09748184075

Private Sub ComboBox1_Change()
Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "mm/dd/yyyy")

End Sub

Private Sub ComboBox2_Change()
Me.ComboBox2.Value = Format(Me.ComboBox2.Value, "mm/dd/yyyy")
End Sub

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim firstdate As Date
Dim timekey1 As Integer
Dim timekey2 As Integer

firstdate = CDate(UserForm1.ComboBox1.Text)
Dim seconddate As Date
seconddate = CDate(UserForm1.ComboBox2.Text)

Sheets("Dates").Select
Range("B2").Select
Do
If firstdate = CDate(ActiveCell.Value) Then

'MsgBox (Replace(ActiveCell.address, "$", ""))

timekey1 = CInt(ActiveCell.Offset(0, -1).Value)
'MsgBox (timekey1)
Exit Do

Else
ActiveCell.Offset(1, 0).Select

End If
Loop While ActiveCell.Value <> ""

Sheets("Dates").Select
Range("B2").Select
Do
If seconddate = CDate(ActiveCell.Value) Then
timekey2 = CInt(ActiveCell.Offset(0, -1).Value)
'MsgBox (timekey2)
Exit Do

Else
ActiveCell.Offset(1, 0).Select

End If
Loop While ActiveCell.Value <> ""
Dim address As String
address = "A" & CStr(timekey1 + 1)
Sheets("Dates").Select
Range(address).Select

Dim address2 As String
address2 = "A" & CStr(timekey2 + 1)
Range(address2).Select
'Range(address & ":" & address2).Select
Dim timekeyarr() As Integer
ReDim timekeyarr(timekey2) As Integer
Dim count As Integer
count = 1
Dim tempaddr As String
tempaddr = address
Range(tempaddr).Select
While Replace(ActiveCell.address, "$", "") <> address2
timekeyarr(count - 1) = CInt(ActiveCell.Value)
count = count + 1

ActiveCell.Offset(1, 0).Select

Wend
'MsgBox (count)
timekeyarr(count - 1) = CInt(ActiveCell.Value)


'For i = 0 To UBound(timekeyarr) - 1
'MsgBox (timekeyarr(i))
'Next i
Dim currencykeyarr() As Integer
ReDim currencykeyarr(UBound(timekeyarr)) As Integer
'MsgBox (UBound(currencykeyarr))

Sheets("Data").Select
Range("B2").Select

Dim temppos As String
temppos = Replace(ActiveCell.address, "$", "")

For i = 0 To UBound(timekeyarr) - 1
Range(temppos).Select
Do
If CInt(ActiveCell.Value) = timekeyarr(i) Then
currencykeyarr(i) = CInt(ActiveCell.Offset(0, -1).Value)
Exit Do
Else
ActiveCell.Offset(1, 0).Select

End If


Loop While CStr(ActiveCell.Value) <> ""
Next i

'For i = 0 To UBound(currencykeyarr) - 1

'MsgBox (currencykeyarr(i))
'Next i
'let's go to the currency sheet and creat the filtered data
On Error Resume Next
Sheets("tempdata").Delete

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(after:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
    ws.Name = "tempdata"
    Sheets("tempdata").Select
'action start
Dim temppos2 As String
Sheets("Currencies").Select
Range("A2").Select


temppos = Replace(ActiveCell.address, "$", "")

For i = 0 To UBound(currencykeyarr) - 1
Range(temppos).Select
Do
If CInt(ActiveCell.Value) = currencykeyarr(i) Then
temppos2 = Replace(ActiveCell.address, "$", "")
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("tempdata").Select
With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Currencies").Select
Range(temppos2).Select
ActiveCell.Offset(1, 0).Select

Exit Do
Else
ActiveCell.Offset(1, 0).Select

End If


Loop While CStr(ActiveCell.Value) <> ""
Next i


Application.CutCopyMode = False

Sheets("tempdata").Select
Range("A1").Select
If ActiveCell.Value <> "" Then

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    End If
    'MsgBox (Selection.address)
    
    Me.ListBox1.Clear
    Me.ListBox1.RowSource = Selection.address
    
    
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Financial calculation using combobox and label in vba,VBA Teacher Sourav,Kolkata 09748184075

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
Dim pos As Integer
Dim result1 As Double
Dim firstval1 As Double
Dim combodata As String
Dim count1 As Integer
Dim covar1 As Double

count1 = 0

If CStr(Me.ComboBox1.Text) <> CStr(Me.ComboBox2.Text) Then
combodata = Me.ComboBox1.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata & """,listrange,0)"
pos = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval1 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result1 = result1 + ActiveCell.Value
count1 = count1 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar1 = (result1 - firstval1) / count1
MsgBox ("count1 is " & count1)
MsgBox ("firstval1 is " & firstval1)
MsgBox ("sum is " & result1)
MsgBox (covar1)
'this is for combobox2


Dim pos2 As Integer
Dim result2 As Double
Dim firstval2 As Double
Dim combodata2 As String
Dim count2 As Integer
Dim covar2 As Double

count2 = 0


combodata2 = Me.ComboBox2.Text
'MsgBox (combodata)

Sheets("Data").Select

'Sheets("Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=MATCH(""" & combodata2 & """,listrange,0)"
pos2 = ActiveCell.Value
ActiveCell.Value = ""
Sheets("Data").Select
Range("A1").Select

For i = 1 To pos2
ActiveCell.Offset(1, 0).Select
Next i
'MsgBox (ActiveCell.Value)
ActiveCell.Offset(0, 2).Select
firstval2 = CDbl(ActiveCell.Value)

While ActiveCell.Value <> ""

result2 = result2 + ActiveCell.Value
count2 = count2 + 1

ActiveCell.Offset(0, 1).Select
Wend
covar2 = (result2 - firstval2) / count2
MsgBox ("count2 is " & count2)
MsgBox ("firstval2 is " & firstval2)
MsgBox ("sum is " & result2)
MsgBox (covar2)
Me.Label1.Caption = CStr(covar1 / covar2)
Else

End If


End Sub

Private Sub UserForm_Click()

   
    
    
    
    
End Sub

Private Sub UserForm_Initialize()
 Me.ComboBox1.List = Sheets("Data").Range("A2:A11").Value
    
 Me.ComboBox2.List = Sheets("Data").Range("A2:A11").Value
    

End Sub

copy data from listbox to listbox in vba,vba teacher Sourav,kolkata 09748184075

Private Sub CommandButton1_Click()
Dim signal As Boolean
signal = False
Dim i As Integer
i = ListBox2.ListCount - 1

 While i >= 0
 'MsgBox (i)
 
 
      
          If UserForm1.ListBox2.Selected(i) Then
          
              'MsgBox (ListBox1.List(i))
          
 For j = 0 To UserForm1.ListBox1.ListCount - 1
 If CStr(UserForm1.ListBox2.List(i)) = CStr(UserForm1.ListBox1.List(j)) Then
 'MsgBox ("wrong")
 signal = True
 Exit For
 End If
 Next j
 
 
          
If signal = True Then

Else
'MsgBox ("getting inside")
UserForm1.ListBox1.AddItem (UserForm1.ListBox2.List(i))
UserForm1.ListBox2.RemoveItem (i)

End If

End If
signal = False

          i = i - 1
           Wend
           
           


End Sub

Private Sub CommandButton2_Click()
Dim signal As Boolean
signal = False
Dim i As Integer
i = ListBox1.ListCount - 1

 While i >= 0
 'MsgBox (i)
 
 
      
          If UserForm1.ListBox1.Selected(i) Then
          
              'MsgBox (ListBox1.List(i))
          
 For j = 0 To UserForm1.ListBox2.ListCount - 1
 If CStr(UserForm1.ListBox1.List(i)) = CStr(UserForm1.ListBox2.List(j)) Then
 signal = True
 Exit For
 End If
 Next j
 
 
          
If signal = True Then

Else
UserForm1.ListBox2.AddItem (UserForm1.ListBox1.List(i))
UserForm1.ListBox1.RemoveItem (i)

End If

End If
signal = False

          i = i - 1
           Wend
           
           

End Sub

Private Sub ListBox2_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Sheets("Data").Select
Dim source As Range
Range("A2").Select
    While ActiveCell.Value <> ""
    UserForm1.ListBox1.AddItem (ActiveCell.Value)
    ActiveCell.Offset(1, 0).Select
    Wend
    
    
    
    
    
    
    
    
End Sub

Sub test()
For j = 0 To UserForm1.ListBox1.ListCount - 1
 UserForm1.ListBox1.RemoveItem j
 Next j
End Sub

French MBA College VBA Assignment done,vba teacher sourav,kolkata 09748184075

Sub advancedfilter()
Sheets("Extraction").Select
Cells.Clear
Range("A1").Select
ActiveCell.Value = "CustomerID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "DateandTime"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Date"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Time"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductID"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "ProductName"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Unit Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Quantity"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Total Price"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Discount"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Final Price"
ActiveCell.Offset(0, 1).Select

'January Sheet data copy


Sheets("January").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("January").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

'February Month Data Copy

Sheets("February").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("February").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


'March Month Data Copy

Sheets("March").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("A")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("B")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("E")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False


Sheets("March").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("Extraction").Select
    With Columns("H")
    .Find(what:="", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

ActiveSheet.Paste


Application.CutCopyMode = False

Sheets("Extraction").Select
Range("C2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=TEXT(RC[-1],""d"")&""/""&TEXT(RC[-1],""m"")&""/""&TEXT(RC[-1],""yy"")"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=TIME(HOUR(RC[-2]),MINUTE(RC[-2]),SECOND((RC[-2])))"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
   ActiveCell.Range("A1:A12000").Select

Sheets("Extraction").Select
Range("F2").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Products!R3C1:R18C3,2,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Products!R3C1:R18C3,3,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select


Range("I2").Select

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    
    Range("J2").Select
      ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Extraction!RC[-1],Discounts,2,TRUE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0%"
    
    
    Range("K2").Select
    
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-(RC[-2]*RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A12000")
    ActiveCell.Range("A1:A12000").Select
    Selection.NumberFormat = "0.00"

Range("A:Z").EntireColumn.AutoFit


End Sub