Just random mumbling

Saturday, March 25, 2017

IDLE's subprocess didn't make connection error in Ubuntu 16 solved,Python Teacher Sourav,Kolkata 09748184075

Basically I had to remove read.py,write.py and pickle.py from my home directory which i have created through coding,i guess they conflicted with some library files having the same names.

Tuesday, March 7, 2017

from a list of companies calculate for each company the logarithmic daily return using yahoo finance and vba,vba teacher sourav,kolkata 09748184075

Option Explicit

Private Sub test_portfolio()
Application.DisplayAlerts = False
On Error Resume Next
Workbooks("table.csv").Close
Workbooks("Part3.xlsm").Activate
   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 tempdateval As Date
   Dim previousclosingrate As Date

   Dim EndYear As Integer
   Dim closingrate As Double

   Dim URL As String
   Dim temppos As String

Sheets("Portfolio").Select
Range("A2").Select

   temppos = Replace(ActiveCell.Address, "$", "")
   Range(temppos).Select
   While ActiveCell.Value <> ""
   temppos = Replace(ActiveCell.Address, "$", "")
   tempdateval = CDate(ActiveCell.Offset(0, 3).Value)


     Symbol = ActiveCell.Value
     ActiveCell.Offset(0, 3).Select




     StartDate = CDate(ActiveCell.Value)

     EndDate = CDate(ActiveCell.Value)


On Error GoTo 0
'StartDate = CDate(StartDate - 1)

     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
     On Error Resume Next

     Workbooks.Open (URL)
       If Err.Number <> 0 Then
       GoTo comingback
       Else

     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

Workbooks("table.csv").Activate

Range("E2").Select
Selection.Copy
Workbooks("Part3.xlsm").Activate
Sheets("Portfolio").Select
Range(temppos).Select
ActiveCell.Offset(0, 4).Select


      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

closingrate = CDbl(ActiveCell.Value)
ActiveCell.Value = 1

ActiveCell.Value = closingrate * (ActiveCell.Offset(0, -2).Value)
ActiveCell.Select
Selection.NumberFormat = "0.00;[Red]0.00"

Workbooks("table.csv").Activate
On Error Resume Next
ActiveWorkbook.Close
End If
Call lastclosingrate(temppos, closingrate)

comingback:

Workbooks("Part3.xlsm").Activate
Range(temppos).Select
ActiveCell.Offset(1, 0).Select
temppos = Replace(ActiveCell.Address, "$", "")

Wend

Application.DisplayAlerts = True

End Sub

Sub lastclosingrate(ByVal temppos As String, ByVal closingratefirst As Double)

On Error Resume Next
Workbooks("table.csv").Close
Workbooks("Part3.xlsm").Activate
   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 tempdateval As Date
   Dim previousclosingrate As Date

   Dim EndYear As Integer
   Dim closingratesecond As Double

   Dim URL As String
   'Dim temppos As String

Sheets("Portfolio").Select

   Range(temppos).Select

   tempdateval = CDate(ActiveCell.Offset(0, 3).Value)


     Symbol = ActiveCell.Value
     ActiveCell.Offset(0, 3).Select




     StartDate = CDate(ActiveCell.Value) - 1

     EndDate = CDate(ActiveCell.Value) - 1



On Error GoTo 0
'StartDate = CDate(StartDate - 1)

     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 UR

     Workbooks.Open (URL)
       If Err.Number <> 0 Then
       GoTo comingback
       Else

     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

Workbooks("table.csv").Activate

Range("E2").Select
closingratesecond = CDbl(ActiveCell.Value)
Selection.Copy
Workbooks("Part3.xlsm").Activate
Sheets("Portfolio").Select
Range(temppos).Select
ActiveCell.Offset(0, 5).Select


      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   If closingratefirst <> 0 And closingratesecond <> 0 Then
   ActiveCell.Value = (closingratefirst / closingratesecond)
   Else
   ActiveCell.Value = "Not available"
   End If



ActiveCell.Select
Selection.NumberFormat = "0.00;[Red]0.00"

Workbooks("table.csv").Activate
On Error Resume Next
ActiveWorkbook.Close
End If

comingback:

Workbooks("Part3.xlsm").Activate
Range(temppos).Select



End Sub

Sunday, March 5, 2017

get stock data for a given date for a list of companies from yahoo finance api(historical data) using vba,vba teacher sourav,kolkata 09748184075

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 = "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

Tuesday, February 28, 2017

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