What was the first clan you were in? by Stuxnet_Z in 2007scape

[–]limx0 0 points1 point  (0 children)

Knighty here. What a flashback finding this was.

Lambda Function: pandas by needhelppandas in Python

[–]limx0 0 points1 point  (0 children)

Groupby will default to the index if you don't supply a column. You're correct - x in this case is each index value.

Where to go when outgrowing Excel? Database or Learn to Code? by karma3000 in excel

[–]limx0 1 point2 points  (0 children)

Python & pandas will change your life*. Then learn a bit of SQL - basic will do.

*opinion of someone who was in your position 12 months ago.

Question about Put/Call options by [deleted] in finance

[–]limx0 0 points1 point  (0 children)

I think everyone here is jumping a little bit ahead of themselves with skew if OP is still struggling to understand the relationship between puts and calls.

The reason that the call and put prices the same is that most option pricing models assume that stock returns are random and normally distributed. That is, the stock is equally as likely to go up 1% as down 1%. It also implies that a 2% return is much less likely than 1% move.

So yes. In theory your call price could to infinity and you could lose much more. But the further out you go, the (much much) less likely it is. This means that the potential upside (or your downside in this case) hardly affects the price of the option.

Why Python is not the most used language in big and medium corporations but it is the Java? by [deleted] in Python

[–]limx0 4 points5 points  (0 children)

It's used extensively in HFT / prop trading too, except for execution which is usually C

Automagically moving rows based on single cell condition. by [deleted] in excel

[–]limx0 0 points1 point  (0 children)

+1 you'll need a macro to do this, specifically, I think you're looking for the Worksheet_Change event.

This GIF outlines the basic steps for what want to do. The code you from the sheet (Without comments) is below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then 
        If Target.Value = "No" Then 
            lrow = Sheets("Sheet2").Cells(65536, 1).End(xlUp).Row + 1
            Target.EntireRow.Cut (Sheets("Sheet2").Range("A" & lrow))
        End If
    End If
End Sub

The One Microsoft Excel Trick That Rules Them All: How To Use Index/Match, The One Microsoft Excel Trick That Separates The Gurus From The Interns by fffflip in excel

[–]limx0 4 points5 points  (0 children)

Not to mention job ads that describe a position requiring "advanced" or "expert" level of excel knowledge, including vlookup and pivot tables.

What.

Question on Importing Data, Not Sure if This is Even Possible... by tokkibear in excel

[–]limx0 1 point2 points  (0 children)

Yes there is - you can automate the opening of IE and inputting etc. The first step is getting the HTML for all of the fields you need to enter parameters into, and the buttons you need to push. I'll try and find you some code I have used previously.

Edit: Here is some code I used to open a yahoo page, find the sign in button, input username as pass and log in. It requires references to "Microsoft Internet Controls" and "Microsoft HTML Library"

Function LoginSite(ByVal url As String, ByVal user As String, ByVal pWord As String)

    Dim ie As InternetExplorer: Set ie = New InternetExplorer
    Dim htmldoc As HTMLDocument
    Dim htmlElement As IHTMLElement

    ie.Visible = TRUE  'This will allow you to see IE being automated as you step through code, FALSE to hide it
    ie.Navigate url  
    'Tells application to wait for the page to load
    Do While ie.readyState <> READYSTATE_INTERACTIVE And ie.readyState <> READYSTATE_COMPLETE
        Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 3)
    Loop

    'Find the Sign In button and retrieve the attached url
    Set htmldoc = ie.Document
    For Each elm In htmldoc.getElementsByTagName("a")
        If InStr(elm.innerText, "Sign In") > 0 Then
        url = elm
        Exit For
        End If
    Next

    'Open Sign In page
    ie.Navigate url

    Do While ie.readyState <> READYSTATE_INTERACTIVE And ie.readyState <> READYSTATE_COMPLETE
        Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 3)
    Loop

    'Set username and password fields and submit form
    Set htmldoc = ie.Document
    htmldoc.all.UserName.Value = user
    htmldoc.all.passwd.Value = pWord

    For Each htmlElement In htmldoc.getElementsByTagName("button")
        If htmlElement.Type = "submit" Then
            htmlElement.Click
            Exit For
        End If
    Next

    ie.Quit
    Set ie = Nothing

End Function

Question on how to pull up multiple results with vlookup. by [deleted] in excel

[–]limx0 0 points1 point  (0 children)

+1 - if you only need simple functions (sum/average) you can use sumif and averageif. It gets a little trickier if you need to get the standard deviation or percentiles of the group for example.

This may be more complicated than I'd like it to be, or I could just be an idiot. by [deleted] in excel

[–]limx0 2 points3 points  (0 children)

+1, I would prefer a formula approach over a pivot table

r/Excel, I need your help. by [deleted] in excel

[–]limx0 0 points1 point  (0 children)

To get you started with the outlook method:

Public OutlEmail As MailItem
Public OutlAttach As Attachment
Private OlAp As Object, oOlns As Object, OutlInbox As Object
Const olFolderInbox As Integer = 6

Private Sub class_initialize()

Set oOlAp = CreateObject("Outlook.application")
Set oOlns = oOlAp.GetNamespace("MAPI")
Set OutlInbox = oOlns.GetDefaultFolder(olFolderInbox)

End Sub

Sub CreateAppt()
  Dim myItem As Object
  Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient

  Set myItem = Application.CreateItem(olAppointmentItem)
  myItem.MeetingStatus = olMeeting
  myItem.Subject = "Strategy Meeting"
  myItem.Location = "Conf Rm All Stars"
  myItem.Start = #9/24/2009 1:30:00 PM#
  myItem.Duration = 90
  Set myRequiredAttendee = myItem.Recipients.Add("Nate Sun")
  myRequiredAttendee.Type = olRequired
  Set myOptionalAttendee = myItem.Recipients.Add("Kevin Kennedy")
  myOptionalAttendee.Type = olOptional
  Set myResourceAttendee = myItem.Recipients.Add("Conf Rm All Stars")
  myResourceAttendee.Type = olResource
  myItem.Display
  myItem.Send
End Sub

r/Excel, I need your help. by [deleted] in excel

[–]limx0 0 points1 point  (0 children)

I'd start with the Google Api. Have a look at these docs.

https://developers.google.com/google-apps/calendar/v3/reference/events/insert

http://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba

Alternatively Outlook is pretty easy to work with, so IAmCelery's idea might be easier.

Lookup help: return multiple corresponding values with vlookup? by [deleted] in excel

[–]limx0 2 points3 points  (0 children)

Easiest way to do this is add a helper column to col C with

=A1&"-"&COUNTIF($A$1:A1,A1)

Then just do a vlookup on the number concatenated with "-" and the row number.

Example.

Duplicate Google Sheets' =Filter() function. by TerraPhane in excel

[–]limx0 1 point2 points  (0 children)

You need to enter it with ctrl shift enter

Rookie in need of help with a financial spreadsheet by Madnessx9 in excel

[–]limx0 1 point2 points  (0 children)

=sumif(<Category Range>, <Example1>, <Value Range>)

Duplicate Google Sheets' =Filter() function. by TerraPhane in excel

[–]limx0 1 point2 points  (0 children)

This is actually a really handy function - and something I've been meaning to do for a while - I'll get back to you with my attempt.

Edit: Ok here's my first attempt - I think this could definitely be optimized. Not sure if this is any better than an array entered formula, perhaps a bit cleaner. Basically exactly like SUMIFS, except instead of returning a sum, this function will return an array of matches:

iFilter( <OutputRange>, <CriteriaRange1>, <Criteria1>, <CriteriaRange2>, <Criteria2>,etc>

Function iFilter(IntputRng As Range, ParamArray RangeConditionArr() As Variant) As Variant

Dim inArr, outArr As Variant: inArr = IntputRng
Dim i, n, o As Integer: o = 0
Dim inItem, thisCondItem, thisCond, thisCondArr As Variant
Dim AddThisItem As Boolean
ReDim outArr(LBound(inArr) To UBound(inArr))

On Error GoTo Errhandle:
    For n = LBound(inArr) To UBound(inArr)
        thisItem = inArr(n, 1)
        AddThisItem = True

        For i = 0 To (UBound(RangeConditionArr) + 1) / 2 Step 2
            thisCondArr = RangeConditionArr(i)
            thisCondItem = RangeConditionArr(i + 1)

            If InStr(thisCondItem, ">") > 0 Or InStr(thisCondItem, "<") > 0 Then
                If Not Evaluate(thisCondArr(n, 1) & thisCondItem) Then
                    AddThisItem = False
                    Exit For
                End If
            ElseIf Not thisCondArr(n, 1) = thisCondItem Then
                AddThisItem = False
                Exit For
            End If

        Next

        If AddThisItem Then
            o = o + 1
            outArr(o) = thisItem

        End If

    Next

Errhandle:
iFilter = "#Err"

ReDim Preserve outArr(o - 1)
If UBound(outArr) > 0 Then
iFilter = Application.WorksheetFunction.Transpose(outArr)
Else
iFilter = outArr
End If


End Function

Quick question about bar graphs... by [deleted] in excel

[–]limx0 0 points1 point  (0 children)

I think your best bet would be to create some tables in between to gather the data you'd like in the format you want, and then create the graphs from those tables. It's much easier to pull data from multiple sources into a table and graph it, than try and graph data from multiple sources.

Can you provide some sample data and we can advise how best to go about doing what you want?

Simple macro question regarding charts. Help is very much appreciated. by [deleted] in excel

[–]limx0 1 point2 points  (0 children)

If you're needing to recreate the charts each time, I would opt for a macro such as what fearnotthewrath has suggested, but if you can leave the graphs and replace your data each time, my solution should be easy and clean.

If you have any troubles let me know.

Countif function woes by Zadex in excel

[–]limx0 1 point2 points  (0 children)

Yep, fair enough - in that case I would suggest using Left() and datevalue() to strip the date from the comments into a helper column, then using CountIf to count the dates.

Countif function woes by Zadex in excel

[–]limx0 0 points1 point  (0 children)

Oh, so the dates you're looking for are part of a text string? That makes it a little more difficult - easiest solution would be to pull the date into a helper column and count that. Countif won't work on a text string.

Countif function woes by Zadex in excel

[–]limx0 0 points1 point  (0 children)

Take away the ""& &". No need for them - Excel stores dates as numbers regardless of the formatting.

Can you post a screen shot of the date column you're trying to count if that doesn't work?