Culling You RSS Feeds

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

Sub ListFeeds()
   
    Dim xml As MSXML2.XMLHTTP60
    Dim sSubUrl As String
    Dim clsFeed As CFeed
    Dim i As Long, j As Long
    Dim xmlDom As MSXML2.DOMDocument60
    Dim xmlListNode As MSXML2.IXMLDOMNode
    Dim xmlFeedNode As MSXML2.IXMLDOMNode
   
    Set gclsFeeds = New CFeeds
       
    'Load an XML document with your google reader subscription list
   sSubUrl = "http://www.google.com/reader/api/0/subscription/list"
   
    Set xml = New MSXML2.XMLHTTP60
   
    xml.Open "GET", sSubUrl
    xml.setRequestHeader "Content-Type", "text/xml"
    xml.send
       
    Set xmlDom = New MSXML2.DOMDocument60
    xmlDom.loadXML xml.responseText
   
    'It's got a lot of stuff, but the 'list' node is the money node
   Set xmlListNode = xmlDom.selectSingleNode("//list")
   
    'Create a new CFeed, fill it, add it to gclsFeeds
   For i = 0 To xmlListNode.childNodes.Length - 1
        Set clsFeed = New CFeed
        With clsFeed
            .Url = xmlListNode.childNodes.Item(i).FirstChild.nodeTypedValue
            .Name = xmlListNode.childNodes.Item(i).FirstChild.nextSibling.nodeTypedValue
            .Folder = xmlListNode.childNodes.Item(i).FirstChild.nextSibling.nextSibling.nodeTypedValue
        End With
        gclsFeeds.Add clsFeed
    Next i
   
    'Since each feeds xml with data points is different, loop through the feeds, reconstruct
   'the xml url and fill the datapoints instances
   For i = 1 To gclsFeeds.Count
        Set clsFeed = gclsFeeds.Item(i)
        xml.Open "GET", "http://www.google.com/reader/api/0/stream/details?s=" & clsFeed.Url
        xml.setRequestHeader "Content-Type", "text/xml"
        xml.send

        xmlDom.loadXML xml.responseText
        Set xmlListNode = xmlDom.getElementsByTagName("list").Item(1)
       
        'This method is little atypical.  I pass it a string and parse the string into
       'a proper object in the class.  It's a bit of a departure than the create-fill-add
       'paradigm used above.
       For j = 0 To xmlListNode.childNodes.Length - 1
            clsFeed.AddDataPoint xmlListNode.childNodes.Item(j).LastChild.nodeTypedValue
        Next j
    Next i
   
    Sheet1.UsedRange.ClearContents
    Sheet1.Cells(1, 1).Resize(gclsFeeds.Count * 2, 64).Value = gclsFeeds.ToRange
   
End Sub

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

Private msName As String
Private msUrl As String
Private msFolder As String
Private mcolDataPoints As Collection

The DataPoints collection holds CDataPoint objects that look like this

Private mdtDataDate As Date
Private mlDataValue As Long
Private msDataType As String

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:

Public Property Get ToRange() As Variant
   
    Dim aReturn() As Variant
    Dim i As Long, j As Long
    Dim clsFeed As CFeed
    Dim lDataCount As Long
   
    ReDim aReturn((Me.Count * 2) + 1, Me.Item(1).DataPointCount + 4)
   
    aReturn(1, 1) = "Name"
    aReturn(1, 2) = "URL"
    aReturn(1, 3) = "Folder"
    aReturn(1, 4) = "Type"
   
    For i = 2 To Me.Count * 2 Step 2
        Set clsFeed = Me.Item(i / 2)
        With clsFeed
            aReturn(i, 1) = .Name
            aReturn(i, 2) = .Url
            aReturn(i, 3) = .Folder
            aReturn(i, 4) = "read"
            aReturn(i + 1, 4) = "posted"
            clsFeed.SortDataPoints
            lDataCount = 1
            For j = 1 To clsFeed.DataPointCount Step 2
                aReturn(1, 4 + lDataCount) = clsFeed.DataPoint(j).DataDate
                aReturn(i, 4 + lDataCount) = clsFeed.DataPoint(j).DataValue
                aReturn(i + 1, 4 + lDataCount) = clsFeed.DataPoint(j + 1).DataValue
                lDataCount = lDataCount + 1
            Next j
        End With
    Next i
   
    ToRange = aReturn
   
End Property

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.

Friday Link Dump

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

Problems related to KB973475 and KB973593

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

Capitalizing Web Addresses

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?

Opening Files on Startup

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

"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls"

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

Private Sub Workbook_Open()
   
    Workbooks.Open "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls"
       
End Sub

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?

2009 in Review

Top 10 Page Views in 2009

Post Page Views
Creating CSV Files 33,947
Sumif Between Two Dates 19,336
Number Stored as Text 15,003
Writing to a Range Using VBA 12,165
Automating Internet Explorer 11,248
Progress Bar 11,212
Testing for Empty Cells 10,581
Pattern Matching 9,665
Calculated Fields in Pivot Tables 9,321
Maxif Minif Functions 8,162

Most Viewed 2009 Posts

Post Page Views
Vlookup on Two Columns 6773
Vlookup 2695
Get the Path to My Documents in VBA 2258
Excel Sample Data 2230
Reading Xml Files in VBA 1710
Bingo 1673
Illegal Range Names 1576
Creating State Maps with XY Charts 1570
Historical US Tax Rates 1564
Work Breakdown Structure Numbering in Excel 1451

Most Commented Posts

Post 2009 Comments
SUMIF Between Two Dates 51
ADO Slower on Linked Tables 33
Removing Spaces from File Names 32
Hello World Button on a Ribbon 30
Wordpress Upgrade 29
One Keyboard Shortcut 29
Illegal Range Names 27
Adding Every Other Cell 27
Summing the Digits of a Number 27
Programming Music 26
 

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

=PERSONAL.XLS!joinrange(C1:D1,"< /td>< td>","< tr>< td>","< /td>< /tr>")

For the comment counts, I used this sql statment

SELECT wp_posts.post_title, wp_posts.post_date, wp_posts.post_name, count(wp_comments.comment_id) AS comment_count
FROM wp_posts INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_id
WHERE year(wp_comments.comment_date)=2009
GROUP BY wp_posts.post_title, wp_posts.post_date, wp_posts.post_name
ORDER BY comment_count DESC

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

New Year’s Resolution: No More Offset

On Simon’s blog, sam comments regarding OFFSET vs. INDEX:

There is a huge performance hit.

You will notice hardly any difference in the Calculation times

But the recalculation times will significantly different in case of OFFSET- to the tune of 25-30 times slower compared Index.

I’ve heard this many times before, but I guess I’m just lazy. I’ve continued to use OFFSET when defining range names. Here’s the code I used to test what he said.

Sub TestVolatile()
   
    Dim wb As Workbook
    Dim aWrite(1 To 65000, 1 To 1) As Double
    Dim i As Long
    Dim clsTimer As CTimer
   
    Set clsTimer = New CTimer
   
    For i = LBound(aWrite, 1) To UBound(aWrite, 1)
        aWrite(i, 1) = i
    Next i
   
    Set wb = ActiveWorkbook
    wb.Sheets(1).Range("A1:A65000").Value = aWrite
   
    For i = 1 To 10
        clsTimer.StartCounter
        wb.Names.Add "MyVol", "=OFFSET($A$1,0,0,COUNTA($A:$A),1)"
        wb.Sheets(1).Range("B1:B10").Formula = "=COUNTA(MyVol)"
        Debug.Print "OFFSET Calc", clsTimer.TimeElapsed
       
        clsTimer.StartCounter
        Application.CalculateFull
        Debug.Print "OFFSET Recalc", clsTimer.TimeElapsed
       
        clsTimer.StartCounter
        wb.Names.Add "MyVol", "=$A$1:INDEX($A:$A,COUNTA($A:$A))"
        Debug.Print "INDEX Calc", clsTimer.TimeElapsed
       
        clsTimer.StartCounter
        Application.CalculateFull
        Debug.Print "INDEX Recalc", clsTimer.TimeElapsed
    Next i
   
End Sub

And here’s the results I got

I must be using a slightly different method, but the results still point to INDEX as being more efficient.

The timer code comes from Mike Woodhouse via StackOverflow.

Software Pricing

Last week I was getting some blue screen errors. A hardware diagnostics test reported that my hard drive had a couple of bad sectors. I bought a new hard drive and set about installing all of my programs. Wow, I have a lot of applications installed.

One of the application is X1; a desktop/email search application. I’m a big fan of X1. I’m not such a fan of the process of reinstalling X1, but that’s another story. When I went to get the client app from their website, I was informed that my license covered version 6.0, while the current version is 6.6. This seemed odd to me as usually minor revision (the number to the right of the decimal) are free upgrades and major versions (the number to the left) you have to pay for. But that’s not the way X1 works. With X1, I pay $50 for the client app and one year of upgrades. Whatever upgrades are available in that year, I get. After that year is over, I have to pay another $50 for another year of upgrades.

This pricing plan isn’t so far out in left field as to be surprising to me. Many software apps charge a maintenance fee that allows you to upgrade the software.
PinkPearl

Quickbooks, for example, can cost $10,000 plus about $1,000 per year if you want to stay current. X1 is a little different, however. I don’t have to upgrade. They don’t seem to have any hook that forces me to upgrade. But if I ever feel the need to, I can upgrade and continue to do so for a year. Another difference is that the X1 “maintenance fee” is the same cost as a brand new license. Most maintenance arrangements are considerably less that the initial outlay.

My interest is how this relates to Excel add-ins. Let’s use Power Utility Pak as an example. It’s a pretty successful add-in that’s sold for actual money. If I pay for PUP v6, I get a perpetual license - that means it lasts forever. I don’t get upgrades, but presumably I can upgrade the version for a discounted price (couldn’t verify that in a quick glance of the product site). Bug fixes and the like are free.

If I buy PUP, I’m good for as long as it works with my current version of Excel. If I bought whatever PUP was selling for Excel 97, it probably still worked just fine in 2003. If I buy the PUP for 2007, it will probably be good for another 10 year.

So what’s better: $40 add-in plus a discounted upgrade fee. Or $40 add-in plus a year of upgrades and if you want another year of upgrades it’s another $40. Oh, and I’m not asking what’s best for the buyer. I’m asking what’s best for the developer. Although the right answer is probably best for both.

I think one of the downsides of the X1 model is the unpredictable schedule of major upgrades. If I buy it now and they release 7.0 in June, I’m getting two versions for the price of one. If they release 7.0 in January 2011, I’m hosed. What then if they release 8.0 in March 2011? It seems unfair and the perception of unfair is bad for business. Should they maintain a consistent upgrade schedule? If they do, does that force them to add features when they probably shouldn’t?

In related news, I read Don’t Just Roll the Dice. It has some basic software pricing guidance in there. Nothing Earth-shattering, but a good read if you’re not familiar with the subject or you just need a refresher.