Total Pageviews

Wednesday, June 22, 2011

Google Spreadsheets Tips: Not Equals, Newlines, And More!

If you are like me, sometimes you just want to add a newline within a cell. And other times, you want to check whether item A is NOT EQUAL to item B within a formula. But dangit... how do you do it!? This entry will cover such tips and tricks as I learn more of them (filtering is pretty sweet too). Without further adieu:
  1. Google Spreadsheets Formula Documentation
  2. Not Equals
  3. Newline
  4. Filtering
  5. Give me the value or give me ZERO... no errors PLEASE!!!

That's the link to all the formulas available in google spreadsheets (hint: click on the title of this very section). It is a pain to find just going to google help (strange....). This first tip is golden! Now you know where the formula list is.
    Not Equals

    There are two ways to do not equals:
    1. Use the symbol: <>
    2. Use the function: NE()
    The first one can be used in calls like the following:
    • =IF(A2<>"SKIP","Fanfare!","--")
    • =SUMIF(B4:B8,"<>2")
    That first one will print "Fanfare!" in its cell if the contents of A2 is not equal to "SKIP". That second one will will sum the contents of the cells in the range B4 to B8 that are not equal to 2.

    Pretty sweet huh?

    Newline

    Found this one searching google help. Check out this answer.

    There are two ways to create a newline within a cell:
    1. Use: &CHAR(10)&
    2. Use shortcut: Ctrl+Enter
      (i.e., press and hold the ctrl key and then press the enter key)
    I typically use the second one.

    Filtering

    Filtering is pretty dang sweet. It allows you to select specific cells of one column, based on criteria against another column. And if you want to get even more crazy, you can add more column/criteria pairs. In fact, you can even do rows, or both columns AND rows (but your brain might explode... just sayin'.)

    Example:

          =2*sum( filter( C2:C40, C2:C40<>"#N/A") )

    This formula will sum all of the values in the column C from rows 2 through 40 where the value within that cell is not equal to #N/A... and then multiply the result by 2.  So if we had the following:

      C  
    2  4  
    3  2  
    4  #N/A  
    5  3  

    The answer would be 2*(4+2+3) = 2*9 = 18.

    What filter basically does is return the values for all cells in the first range where the corresponding cell in the criteria range meets the criteria.

    Let's look at another example:

          =2*sum( filter( C2:C40, C2:C40<>"#N/A", D2:D40<>3) )

    This formula will sum all of the values in the column C from rows 2 through 40 where the value within that cell is not equal to #N/A and the corresponding cells of column D from 2 through 40 is not equal to 3. The result is then multiplied by 2. So if we had the following:

      C    D  
    2  4    3  
    3  2    0  
    4  #N/A    2  
    5  3    9  


    The answer would be 2*(2+3) = 2*5 = 10. 

    Mull that one over a bit.  But yes, filter is bad @$$.

    Give me the value or give me ZERO... no errors PLEASE!!!

    Answer: N( IFERROR( value ) )

    This combination of functions is magical.  It will return 0 if whatever you are trying to do for value bombs big time. But, if it doesn't bomb, this combo will return value.

    Why would we want to do this? Check out the following data set:

      C    D    E  
    2  a    inc    3  
    3  b    inc    1  
    4  a    dec    -2  
    5  b    add    9  

    Okay, so we've got something identified by the letter "a" and something identified by the letter "b". We also have indicators that suggest incrementing (inc), decrementing (dec), and adding (add). In this data set, inc and add mean the same thing. So to get the total amount of a particular item in column C, we need to some the values in column E where the corresponding cells in column D equal inc or add.

    Whew..... got that?

    Let's take the item designated as "b". If we wanted to find the total amount of it, we need two sums:

    • sum of all rows in E corresponding to item "b" designated as inc
    • sum of all rows in E corresponding to item "b" designated as add

    So, we filter on column E where column C="b" and where column D="inc".  Then we filter again except change "inc" to "add".

          = sum( filter(!E:E,!C:C="b",!D:D="inc") )  + sum( filter(!E:E,!C:C="b",!D:D="add") )

    This will gives us the answer 9. Great! Now, if we do the same thing for "a", the answer will be #N/A. Why? Because there are no rows in the data set for item "a" designated as add. So the filter won't return anything, and sum will throw an error.  We don't actually care that there were no rows designated as add. In fact, we would just be happy if it returned 0.

    BAM! That's where N( IFERROR( value ) ) swoops in like Superman and saves the day (or like Deadpool with guns blasting). If we wrap each sum method in N( IFERROR( ) ), we will get the result of 3 for "a". WIN!

    There may be a simpler way. If you know it, holla at me.

    Tuesday, June 07, 2011

    When do Options Expire?

    This is a long time coming. I think I've had my reference articles opened in Chrome tabs for a year now (yeah, you read that correctly... one year). So, when do the financial instruments known as options expire? The answer isn't a simple one. It depends on what type of option we're talking about, and potentially on what underlying to which this instrument derives its value. This article aims to distill the answers in as concise a way as possible. Here goes:

    Option TypeOption FrequencyExpiration Last Trading OpportunitySettlement Reconciled Proviso
    Equity Weekly Friday Fri @ 16:00H Fri @ 16:00HFri @ 17:00H
    Equity Monthly Saturday following the third Friday of the monthFri @ 16:00H Fri @ 16:00HSat @ 17:00H
    Index (PM) Weekly FridayThu @ 16:00H Fri @ 16:00HFri @ 17:00H
    Index (PM) Monthly Saturday following the third Friday of the monthThu @ 16:00H Fri @ 16:00HSat @ 17:00H
    Index (AM) Weekly FridayThu @ 16:00H Fri @ AM Fri @ 17:00HSettlement computed once all opening prices of all underlyings that represent this index are recorded
    Index (AM) Monthly Saturday following the third Friday of the monthThu @ 16:00H Fri @ AM Sat @ 17:00HSettlement computed once all opening prices of all underlyings that represent this index are recorded
    *All times above are Eastern Standard Time (EST)

    Allowing the option to expire "worthless" is a bad idea if the price is anywhere near the strike. Due to settlement and reconciliation processes, the option you thought was out-of-the-money may actually end up being in-the-money (and vice-versa). This is particularly bad if you are unhedged in a short position or don't have the funds in an account where a long position is automatically exercised.

    Other Facts

    American Style Exercise: Options can be exercised anytime before expiration
    European Style Exercise: Options can only be exercised at expiration

    • When holidays occur, last trading dates and index settlements get pushed earlier one day (as expected)
    • Equity Options have American Style Exercise
    • Index Options have European Style Exercise -- some are American

    Sources

    Options Expiration and Exercise
    Options Expiration FAQ
    CBOE Glossary
    Options Don't Expire on Fridays
    What are the terms of Short-Term Options?

    Sunday, June 05, 2011

    How to Permanently Delete Images in iPhoto

    Did you know... iPhoto has it's own trash can!? Yup! Like you, I did not realize this fact. iPhoto does so many things under the hood, it's hard to know what exactly is going on. Did you know that if you do red-eye removal on a photo, a new copy of that photo is made? iPhoto wants to make sure the original photo stays intact so that we, the users, may always have access to it. Okay, let's get on with the purpose of this post.

    Album:
    • Is a "view" on the photos in your library
    • Think of albums as "Labels" in GMail
      • many labels (albums) can point to the same e-mail (photo)
      • you can create as many labels (albums) as you want
      • deleting a photo from an album is akin to removing a GMail label from an e-mail -- that e-mail (photo) is simply removed from that view (label/album), but the e-mail still exists in your library
      • deleting a label (album) does not delete your e-mails (photos); it only removes the association
    Library:
    • all of your photos
    • deleting a photo in your library moves it to iPhoto's trash -- i.e., it still exists

    So far, I hope the distinctions have been clear between deleting a photo in an album versus deleting a photo in your library. Once you have deleted a photo in your library (and it has been moved to the trash), you must EMPTY THE TRASH to permanently remove the photo from your computer. There are three ways to do this:

    1. Use the main menu bar


    a) click on "iPhoto" in the main menu
    b) click on "Empty Trash"
    c) FANFARE!!!

















    2. Use the trash icon


    a) look under "Recent"
    b) right click on "Trash"
    c) select "Empty Trash"
    d) FANFARE!!!




    (Note: the icon will appear under "Recent" if you've recently viewed/emptied trash)




    3. Use a shortcut


    a) make sure you haven't selected any images anywhere
    b) press shift-command-delete