Small Thought – They Shouldn’t Be Spreadsheets
The Small Thought Blog has the premise that Spreadsheets are the wrong tool for the job (”specialized timesheets, customer databases, todo lists, shift schedules, project charts, contact lists, issue trackers”), however it will try and answer the question “if they shouldn
The Excel Maniacs have an Excel Chatroom.
Just needs some chatters…….
An addin to integrate the chat with Excel could also be interesting (you could chat from the web or inside an Excel popup/form).
Eileen Brown posts a list of the Office Webcasts for April.
One you may be interested in: Complex Excel Charts Made Simple (Level 200)
I was browsing the Webcast archives and found an Excel XML webcast that looks interesting:
Support WebCast: Using XML in Microsoft Office Excel 2003
I haven’t watched it yet.
The new Office Zealot Podcast is up:
Show #2: Interview with John Durant of the Office Developer Center on MSDN
The second part of the show is interesting when the discussion turns to VBA/.NET and John recommends office developers consider learning XML and .NET
Here are two additional links mentioned in the podcast:
Convert VBA Code to Visual Basic .NET When Migrating to Visual Studio 2005 Tools for Office
I’ve been researching Excel XML the past couple days.
I started off with the goal of finding an easy way to filter XML data BEFORE it hit the spreadsheet. For instance, instead of dumping all of the XML to a list, then filtering it, I wanted to “query” the XML something like this:
(SELECT * FROM XML WHERE Record LIKE ‘Airport’)
similar to how I would query a database (in theory), however I was unsuccesful (I believe it’s possible to do it in the Schema, but that’s very user unfriendly). Also, XPath Predicates with a filter(see links) could possibly be what I’m looking for, however I couldn’t nail down the proper syntax.
A few links I found along the way:
Using the Excel 2003 Object Model to Add XML Data Integration (I could import and map XML to a repeating list with Xpath, however I couldn’t figure out the filter syntax(see XPath Predicates)….what is @foo?)
Microsoft Office Excel 2003 Preview (see XML Programmability)
W3Schools (lotsa XML links on the left menu)
XML Standards Reference (Xpath is there also)
Transform XML Files with XSLT When Importing into Microsoft Excel
Using Microsoft’s XMLHTTP Object to Get Data From Other Web Pages
Determine the Version of MSXML Parser Installed on a Computer
I created an Excel macro to post data to our WebService servlet.
You can subscribe to the Microsoft Excel Newsgroups by using the Atom feed from Google Groups. This means you can read them in software like Bloglines or Newsgator. As a matter of fact, all of the Google groups are syndicated:
Google – “To view a group’s feed information, go to the group’s homepage and click the “About this group” link under the title.”
Here are a few links to Excel Newsgroups in Google, look for the atom feeds in the middle of the page:
microsoft.public.excel.worksheet.functions
microsoft.public.excel.misc
microsoft.public.excel.programming
microsoft.public.excel.charting
Meadinkent has a description and some VBA code to convert an Excel table to an XML file.
Just input what to call the file, the record name, location of headers and data and you’ve got an XML file.
I put together a quick demo of getting Inet data into Excel.
The worksheet requires Excel 2003 pro and a developer token from Inet (after you register for Inet, click the Get Developer Token Link).
As the title states it’s a barebones example, you can download it here. (you need to enter your developer token in cell B1). The spreadsheet gets the last transaction info, and downloads the buy/sell book orders to a single list. You can build from there, I’ll post what I come up with when I get some free time.
What’s interesting is the Inet data in Excel is more accurate than their online viewer. Here is a picture of the Inet Spreadsheet vs. the Island viewer for the stock Google (notice the online version is delayed, however the Excel version was real time):
If you do use this for anything or have ideas on what to do with the data, leave a comment.
I haven’t placed a stock trade in over a year, and when I did it was only a hobby and I usually gravitated towards Nasdaq tech stocks.
One of the sites I frequented was Island.com (INET) – “Island accounts for a leading percentage of Nasdaq market volume and is often the number one market participant in such popular stocks as Yahoo!, Cisco and Intel.”
I’m guessing 60% of the trades I made showed up in the Island book.
This evening, by chance, I discovered that INET now has a REST web service, it’s FREE, and has a whopping 100,000 query/per day limit. Very cool. This should be a very easy way to get some real time stock data into Excel.
I’ll put together a simple Island Book Viewer in the morning when the market is live.
Aside: I rarely ever used Excel for analyzing stocks, I preferred Wealthlab. There wasn’t any scenario I couldn’t test with a little code, and there is a large community/codebase too. The only drawback was I had to learn the basics of programming Delphi.
Also, If you do use Excel to analyze stocks, and don’t mind converting Delphi to VBA, Weathlab has a large repository of scripts publicly available over here.
Tom | 
03 31st, 2005| 