Curious Case of a Stuck Price Quote

The number of gotchas you can come across while trading in the financial markets are innumerate. The latest I've come across on my merry journey: the almighty stuck price quote!

BAM! See it? I met this happy traveler back on Thursday, December 6th. There I was, minding my own business and watching the quotes meander as they oft do. Suddenly, I see the bid far surpass the ask! And what do you think my first thought was? Well, it definitely wasn't, "That can't be right... let's find out what's going on." No, my first thought was more, "Whoa! Quick, adjust my offer price in the off chance it might be real!"

In the trading world, there are times when you shoot first and ask questions later. Remember the AAPL flash crash in 2010? I do! My long puts got pretty fat with a quickness, and I sold them just as fast. Now with profits safely taken off the table, I had plenty of time to ask questions. This scenario, however, wasn't the same case. No one gobbled up my puts.

My net liquidating value looked AWESOME. But that was all fake. Now it was important to find out what the deal was so that I could know where the market was actually trading. A quick call to the broker and it was all clear, "Looks like the quote is stuck. I'll report it to the exchange." Did he just say, "stuck?" Better believe it. In fact, that bid price remained at $30.30 for a good long while.

Moral of this story, watch out for stuck quotes! It is bad news, especially if you wrote a program to handle your trading for you. A stuck quote means could mean your program has a false idea about the state of the world. Add this quirk to the list of errant conditions for which you are checking!

How To Obtain The Day of Week Given a Date in Google Spreadsheets

UPDATE 2017-10-12:
As noted in the comments, there is an even much easier way to do this now!

=TEXT(A1,"ddd")

At some point Google updated the text function to take formatting parameters which makes this a cinch.

The old post from 2011-10-09 follows below and is a great introduction to the CHOOSE function. But don't use it for getting the day of week anymore!

---

Google Spreadsheets has a nifty function named CHOOSE which exhibits behavior like the switch statement programmers all know and love. Basically, it allows one to select an action/value based on some input. This is GREAT!

I needed to fill cells with the day of the week given a date. There isn't any built-in function that performs this task in Google Spreadsheets right now. But, we can solve this very easily using the CHOOSE function.

=CHOOSE( weekday(H1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

This formula is very simple:

1. The contents of cell H1 holds the date
2. The function weekday converts that date to a number from 1 through 7 representing the day of the week (Sunday=1)
3. This value, in turn, is then used by CHOOSE to select which value in the subsequent list to use in the current cell
4. Voila

E... to the Z.

In this case, we are converting dates to days of the week in the format I wanted. Now we can start getting all fancy. Imagine, instead of days of the week, the list of possible values were different formulas to compute some result. The formula to use depends on the value inserted in the first input to CHOOSE.  However, remember that nothing is stopping even this first input from being yet another formula computing a specific result based on the data in particular cells. 

With this function, the possibilities are endless!

Reference: See the second comment on this blogpost