Daily Dose of Excel

I had the unfortunate opportunity to learn how cash advances on credit cards work. The particular card I saw was from Capital One, but I think they all work the same. There are three important things to know about cash advances:

1. The interest rate is huge - usually in the mid-20% neighborhood
2. Interest starts on the day of the advance - no grace period like with purchases
3. Payment are applied to the purchases first, then cash advances, even if the purchase occurs after the statement date

I knew about points 1 and 2. I did not know about number 3. Even knowing 1 and 2, I still would not have discouraged this person from getting a cash advance, because I know she pays off her balance every month. Capital One can charge 25% or 50%, what difference does it make. She’ll pay the balance at her next statement and she’ll have some small interest due. Except for #3. Number 3 ensures that she will be paying interest on that cash advance for the rest of her natural life.

I should mention that I still discourage taking cash advances on credit cards. Even without #3, it’s better to get your cash in other ways. But if you’re out of town and lost your ATM card and can’t get to a bank branch - in other words it’s a freakin’ emergency and there’s no other way - then I would have said ‘Go for it’. Now I know that there is no situation so dire that you should take a cash advance on your credit card. Go find a pawn shop and borrow money from a loan shark - it’s cheaper.

Based on the three points above, I was wondering how long it would take to pay off the cash advance. The interest on each statement is not that big. If you’re the type of person who carries a balance, you probably wouldn’t even notice that some of the finance charge is coming from a cash advance. Thankfully, this person has zero finance charges every month, so it was pretty easy to spot.

Here’s the scenario: A person spends $100 per week on his credit card and pays the balance every statement. Statements cut off on the 18th of the month and payment is due on the 28th. On January 15th, in addition to the normal $100 per week, he takes a $500 cash advance. How long will it take to make the cash advance balance zero? The answer is never. Under these conditions, the cash advance balance will never be zero. Let’s check out the model.

Column B: The total payment made - always the statement balance. On 1/28/2010, $601.02 is paid because that’s the balance on the statement date.
Column C: Purchases made - $100 every 7 days. A little contrived, but what can I do.
Column D: The amount of column B that the credit card company applies to purchases. On 1/28/2010, they apply the payment to the purchase balance on 1/27/2010 - not the purchase balance on the statement date.
Column G: The amount of column B that the credit card company applies to cash advances. Even though the whole $501.02 of cash advances from the statement date was paid, only $401.02 is applied because there were purchases in the interim.
Column H: The daily interest on cash advances. There is no interest on purchases because we pay the whole balance every month. The interest rate is in H1.

Note that I’ve hidden some rows in the above image. The next payment, 2/28/2010, is applied 100% to purchases and the cash advances balance just keeps growing. Ten years later, the $502.17 payment reduces the cash advance balance to $100.70. Ten years later! In the mean time, the total interest paid was $260.11 on a $500 cash advance. Pathetic.

Everything in this model is pretty solid. I don’t cover the 3% gouge he got when he took the cash advance. And the $100 per week is an average spending pattern that’s been rigidly applied. I wonder what would happen if I made the spending habits a little more random.

The formula in C4 is =IF(WEEKDAY(A4)=6,100,""). Let’s see what happens when I change it to =IF(RANDBETWEEN(1,7)< =3,RANDBETWEEN(25,40),"").

Here are the results for 10 recalcs

In a lot of cases, the only way to get rid of the cash balance is to stop using your card for a while.

You can download CashAdvance.zip.

I got the error message “Margins Don’t Fit Page Size” when I tried to Print Preview after setting the scaling to Fit to 1 pages wide by 1 tall. At 100% scaling, the spreadsheet took six pages to print. But at 1×1, it took 12,438 pages to print. Here’s Page Break View:

I was dumbfounded. Gobsmacked, if you will. I’d never seen this error before and I’ve worked in Excel for a few years. You might say that I have above-average experience with Excel. I just assumed I’d seen everything.

A Google search didn’t bring up too many results, but TNPC had the answer.. In Windows XP, I went to Start - Programs - Accessories - Disk Cleanup and cleaned out 45MB of Temporary Internet Files. I restarted Excel and all was well.

Last month, Debra lamented:

It’s a tedious process to go through the list individually, especially since the Show Details doesn’t stay open.

I started working on a solution, but quickly grew tired of it. Then today, The Grumpy Old Programmer posted some ruby code to find the date certain blogs were last updated. It renewed my interest, so I pulled out my old code and had another look.

Right now, I’m just dumping the raw data that makes up the Show Details graph from Google Reader. It needs some more work to make it more easily readable. Here’s a sample of what the output looks like now:

And here’s the main code

You definitely want to use this code at your own risk. Also, it takes a really long time to run. Apparently the Google API uses the cookies on your computer for validation. For this code to work, I have to open Internet Explorer and login to Google Reader. Here’s an overview of what the code does:

I have a couple of custom classes, the most important of which is CFeed. Each CFeed instance holds all the data for one feed. Here’s the declaration section

The DataPoints collection holds CDataPoint objects that look like this

The DataType property is either “read” or “posted”.

Arnie Almighty showed me the URLs I needed to get at the xml that holds all the data. You know what’s more fun that parsing XML? Everything. Anyway, I fill all my classes by looping through the XML file and getting the data I need, including the all important URL. Then I loop through all my CFeed objects and use the URL property to get yet more XML that contains the data points. Finally, with all my classes filled, I create an array to write to the sheet. Here’s the ToRange property of the CFeeds class that produces the array:

For some reason the December dates are coming out as 2010 and so the sort is off. Also, I’m assuming that the XML is consistently showing “read” before “posted” so I’m not checking or sorting on that value. It’s true for every one I’ve checked, but if this code were to ever become useful, I’d want to lock that down. Actually, looking at the data in the above image for WOWT, it’s pretty clear that ‘read’ and ‘posted’ are not consistent.

You can download GoogleAPI.zip and have a look at the code if you like.

Cloud Excel

Excel Web App 2010 Overview
Editing in Excel Web App – Part I

I haven’t used the web app yet, so take my comments in that light. Fidelity appears to be the word of the hour. Clearly MS thinks it’s important, but I’m not so sure. I don’t expect my bank’s website to look the same in Firefox as on an iPhone. When I’m using a “lesser” medium, I expect some reduced functions. And by reducing those functions, the overall experience is better because I’m able to use the core functions more easily.

Parallel that with Excel on the web. Will ever higher fidelity result in some lack of usability? Will it be like being stuck in an over-javascripty website and feel like they’ve crammed non-web stuff onto the web? If MS spends too many resources on fidelity, are they spending enough on features? These are the questions that interest me.

I’d also like to hear about navigation. I don’t make even simple lists on Google spreadsheets because I don’t like navigating around them.

Databases

The Database Design Process

I’ve bookmarked this post for later reading. I didn’t know that I needed to rethink my database design process until I read it and the first several comments. Clearly there are people way smarter than me who have thought this through. I intend to learn something.

Licensing

Via Alex & Access, Licensing Office in Terminal Server.

Here’s a quote

You can have Office Pro 2003 on the TS and permit access from Office Pro 2003, Office Pro 2007, Office Enterprise 2007, HUP and FPP Office Ultimate but you cannot have Office Pro 2007 on the TS Server and permit access from Office Pro 2003!!!, it must be Office Pro 2007 or Office Enterprise 2007, HUP or FPP Office Ultimate

But what about TPS reports? Seriously, the only conclusion I can reach after reading that article is that Microsoft has no love for the little guy. I have a Terminal Server and it works great. But I don’t have Office on there. I can’t afford to hire a full-time person just to figure out the licensing. If Microsoft wants to score big with me, they need a new product - Microsoft Office Terminal Server Small Business Amnesty Pack. It’s more than five words, so I know marketing will like it. This product will allow five specific users to use Office on a TS. It won’t matter what version of Office they have or what version is on the TS. It won’t matter if they’re concurrent. It’s basically free reign to use Office in a small shop without worry. If you need six licenses, then you have to do it the old way. I’d buy that.

Training

DataPig announces his 2010 Training Schedule. I’m on the schedule and really looking forward to it.

Marketing

PeltierTech uses the power of hot chicks. Walkenbach does not. The aforementioned DataPig takes it to the next level. He not only has the picture, he’s named the woman. Heather says… buy my stuff. Awesome. Although Heather looks a little blurry for this early in the day. Women don’t usually start looking that blurry until midnight. Considering the expression on her face, can you guess which program she’s using?

Photo by

Hi All,

Many users (including customers of mine) have experienced trouble with two recent security updates by Microsoft, see:

http://support.microsoft.com/kb/973475

and

http://support.microsoft.com/kb/973593

There seem to be two hotfixes to this problem:

KB973475 Excel 2003 hotfix package :

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=978908&kbln=en-us

KB973593 Excel 2007 hotfix package :

http://support.microsoft.com/default.aspx?scid=kb;EN-US;978522

If you experience problems with your Excel, check if you have this update and if so, download the hotfix.
For other problems related to starting and stopping Excel, see:

http://www.jkp-ads.com/Articles/StartupProblems.asp

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

In written correspondence, which is best:

1. dailydoseofexcel.com
2. DailyDoseofExcel.com
3. DailyDoseOfExcel.com
4. www.dailydoseofexcel.com
5. www.DailyDoseofExcel.com
6. www.DailyDoseOfExcel.com
7. http://dailydoseofexcel.com
8. http://DailyDoseofExcel.com
9. http://DailyDoseOfExcel.com
10. http://www.dailydoseofexcel.com
11. http://www.DailyDoseofExcel.com
12. http://www.DailyDoseOfExcel.com

I personally prefer #1, all lowercase and no unnecessary prefix. But I think I’m in the minority. One of the capitalized ones is probably easier to read, but I’m no sure about the “of”. Thoughts?

Let’s say that you want to open a specific file when you start Excel. Here are some options:

XLSTART

If you have Excel installed, you have a folder called XLSTART. Mine’s here C:\Documents and Settings\Dick\Application Data\Microsoft\Excel\XLSTART. On my Windows 7 machine running Office 2010 Beta, it’s here C:\Program Files\Microsoft Office\Office14\XLSTART\. (Are we putting data files under Program Files again?)

Any file you put in XLSTART will open automatically when you start Excel. The next time your coworker leaves his workstation unattended, put a couple hundred CSV files in his XLSTART folder. You’ll be the office comedian.

Alternate Startup Location

2003: Tools - Options - General - At startup, open all files in:
2007: Office Orb - Excel Options - Advanced - General - At startup, open all files in:
2010: File - Options - Advanced - General - At startup, open all files in:

Works just like XLSTART, but you can leave this textbox blank (it is by default).

Here’s some other stuff I wrote on XLSTART and Alternative Startup Locations.

Shortcuts

You can make a new shortcut with the following Target

That will open multiplelookups.xls whenever you use this shortcut to open Excel. You could also just use the file name without the executable as long as your file associates are correct. If you don’t know what file associations are, they’re probably OK.

Auto_Open

Do you already have a workbook or add-in that opens automatically? I have a bunch, but the easiest one for me to modify is PERSONAL.XLS. I can create a new module in my PMW and put in a macro that opens a workbook by following these steps:

1. From Excel, press Alt+F11 to open the VBE
2. Press Control+R to show the Project Explorer if it’s not already showing
3. Find PERSONAL.XLS (or whatever workbook opens for you at startup) in the Project Explorer and select it. Make sure that the file name in the title bar is correct.
4. From the Insert menu, choose Module
5. In the code pane, type something like
   Sub Auto_Open()
      
       Workbooks.Open "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls"
      
   End Sub

   Make sure it’s called Auto_Open because that’s Excel’s trigger to run it on startup.

6. Still in the VBE, from the File menu, choose Save PERSONAL.XLS
7. Press Alt+F4 to close the VBE and return to Excel

Now whenever you start Excel, the Auto_Open code will run and your file will open.

Open Event

This is like Auto_Open. Instead of inserting a new Module, locate the ThisWorkbook module and open it. At the top of the code pane, there are two drop down boxes. Select Workbook from the left one and Open from the right one. Was Open already in the right one? That’s OK. Now type that same one line of code from above so that your module looks like this

Like Auto_Open, this code will run whenever PERSONAL.XLS is opened.

That’s all the ways I can think of to open a file when Excel opens. What did I miss?

Top 10 Page Views in 2009

Most Viewed 2009 Posts

Most Commented Posts

For the first two tables, I copied tables out of Google Analytics, messaged them with Quick TTC, and created the html tables with JoinRange, like

For the comment counts, I used this sql statment

I probably should have only included approved comments, but it’s close enough for government work.