Wednesday, July 24, 2013

The real cost of AA batteries

Anyone who has kids, or even just a bunch of electronics, has felt the pinch of the cost of batteries.

In a google search today, I found a report from WWBT (NBC 12 in Richmond, VA) listing the reporter's data from a flashlight experiment where she tested many different brands of AA batteries.

Her report provides a table of the type of battery, the cost of each, and the length in hours each burned.  But that didn't answer my question:  Which brand of battery is the best deal?

So using her data, I created my own spreadsheet and got some answers.

It is important to note that this is pretty unscientific.  For example, I added the Sunbeam 8-pack (for $1) from the Dollar Tree.  This is what we typically use around my house for kids toys, wireless mice, etc.  I made the (hopefully correct) assumption that these are the same as the 4 to a pack sunbeam batteries from this reporter's test.  This test also completely ignores the fact that batteries perform differently in different types of electronics.  For example one battery may perform better in a device with a low energy consumption, and another may work better in one with a high energy consumption.

But even for an unscientific test, the results are pretty interesting.

Here is what I came up with -- sorted with the best deals at the top.

Store Brand Price # in Package  Cost per Battery Hours Lasted2 Cost per Hour
Dollar Tree Sunbeam (8 to a pack variety) $1.00 8 $0.13      4.50  $     0.03
Costco Kirkland $12.79 48 $0.27      5.85  $     0.05
Dollar Tree Sunbeam $1.00 4 $0.25      4.50  $     0.06
Dollar Tree Eveready Gold $1.00 4 $0.25      3.55  $     0.07
Dollar Tree Panasonic Alkaline $1.00 4 $0.25      1.98  $     0.13
Walmart Duracell $3.77 4 $0.94      5.93  $     0.16
Dollar Tree Panasonic Super Heavy Duty $1.00 2 $0.50      2.77  $     0.18
Walmart Energizer $3.77 4 $0.94      5.18  $     0.18
Rite Aid Rite Aid $4.49 4 $1.12      5.45  $     0.21
CVS CVS $4.39 4 $1.10      5.15  $     0.21
Walgreens Walgreens $3.99 4 $1.00      4.65  $     0.21

Sunday, July 7, 2013

Fetching Stock Quotes with Excel 2013 and Windows 8

Unfortunately, the MSN Stock Quote tool that I have been using for years in Microsoft Excel no longer works for fetching stock prices in Excel 2013.  There are some alternatives out there -- namely this option to enable the PSQ function -- however, that solution doesn't seem to work in Windows 8.

Luckily, I have finally found a solution thanks to this blog and the little known WEBSERVICE function in Excel.  Using the Yahoo Stock quote API, you can easily write a formula that can be copied (or "Filled Down") to adjacent cells.

Assuming your ticker symbol is in A1, to fetch a price, a basic formula would be:
=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1")

Oddly enough, Excel returns this not as a number, but as text.  We can fix this easily by rewriting the formulas as:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

The parts to pay attention to are at the end.  s="&A1&" tells it that the stock ticker is in A1.
&f=l1 tells it to return the last trade price.

To fetch the name of the stock, you would substitue l1 with n.

So for example, if you wanted to have 3 columns with ticker, name, price, you could accomplish this by doing the following.

A1=GOOG
B1=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&A1&"&f=n")
C1=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

So now just setup your spreasheet and use Fill Down (Control D) to fill the columns.

UPDATE:  As of May, 2017, Yahoo changed the address.  I have updated the address above.  However, if you had it working previously, you may need to change the address in the formula from http://finance.yahoo.com to http://download.finance.yahoo.com.  Thanks to a reader for figuring this out and posting the solution in comments.

If you already have your spreadsheet setup with the old method, you can do a 'replace all' by changing "/finance" to "/download.finance".