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.
This is really cool. Colo and friends that run the JMT Excel Forum have added an RSS feed.
To subscribe to the feed you’ll need to copy and paste the following url into your reader:
www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?action=rss
If you use Bloglines you can subscribe by clicking the following button:
If you use MyYahoo you can subscribe by clicking the following button:
Wow, I’m light years behind with the XML stuff…
Automating the XML Data Mapping Process in Excel 2003
Time to get started: Does anybody have any good Excel XML links or resources worth mentioning?
There are 2 sources for XML maps I know of:
1.You have a copy of the XML data you wish to map (XML file)
2.You know the location of the XML’s schema (XSD file)
In both cases, it’s simply a matter of pointing Excel to either location. In the first case Excel guesses at the Map, in the latter it uses the data in the Schema to create the map.
For this example lets assume we want to Create a Map for the XML data returned from the Yahoo Web Search Service.
This is a good example to demonstrate each method becuase they have a sample of the XML data available, and they also have the Schema available, and both work to create the Map.
Note: Keep in mind the XML or XSD files need not be at a url address, they can be saved on your local drive.
Create a Map in Excel
1. On the main menu goto Data->XML->XML Source
2. Click the XML Maps Button
3. In the newly opened dialog box click the Add button
4. Enter the location of the XSD or XML file you wish to Map
Note: If you are trying to paste the URL into the File Name box, you can’t right click in the box, you must use the paste shortcut (Ctrl-V)
5. Click the OPEN button.
That’s it, you should have an XML Map in your workbook based on the XML or XSD file you designated. Here’s what the map I just added looks like:
Now that I’ve determined there is no need to use WinHTTP with basic REST WebService requests in Excel, I’ve updated the Yahoo Image Search Spreadsheet to use XmlMaps.Import instead.
Same result, a few less lines of code: Download V2
It looks like the #1 result for “DJ” has changed since last week.
Somehow I’ve always missed this page on visits to Colo’s site:
Colo’s Excel Junk Room - Downloads
Includes his HTML maker, a list of Utility Downloads, and and a larger list of Excel games.
For some reason the wires never properly crossed that; because you can import XML to Excel directly from a URL, there is no need to use WINHTTP to do simple REST web service queries.
I originally started using the technique after viewing the Amazon Web Services code that uses WinHTTP, however it’s not necessary for only requesting the XML from a web service.
Proof of Concept: Here is a barebones example of using the Yahoo News Search API to get the latest Yahoo news on “Dan Rather” into Excel. This requires a version of Excel that supports XML:
1. Highlight then Copy this URl:
http://api.search.yahoo.com/WebSearchService/V1/webSearch?appid=YahooDemo&query=Dan+Rather&results=10
2. In Excel go to Data->XML->Import
3. In the File Name box use the Paste Shortcut (CTRL-V) to enter the url you just copied and hit enter.
4. Excel will prompt you for the cell to list the Yahoo News Results
So we just managed to use a the Yahoo Rest Webservice to get data into Excel, without WINHTTP. Keep in mind that’s a bare bones example. To expound on it, add a map to the workbook and use XMLMaps.import instead of the WINHTTP examples I’ve been using.
When would I currently use WINHTTP with REST webservices? My best guess so far:
1. When login is required for the service. In which case I need to use SetCredentials
2. When I don’t want to import the XML data to an XML map, rather I just want the raw data. For instance I want to manipulate the data before writing it to a spreadsheet or possibly navigate the XML and pick and choose which data to display based on custom criteria.
Finally, XMLMAPS.Import and WINHTTP both work for consuming REST web services, however the latter requires a few more lines of code. I’ll keep stumbling around with the Web Services untill I learn it (and of course: get it correct). I wonder if anyone else has any REST examples using Excel?
“Microsoft remains “firm” in its plans to end free support for Visual Basic 6 at the end of the month”…Microsoft walks VB tight rope
A good overview article, with links, on the happenings around MS ending VB6 support in a couple weeks.
Another interesting quote:
“Surveys by Evans Data indicate that the number of VB6 developers outnumber the people who have learned VB.Net. Forty-four percent of developers report working with VB, while 34 percent work with VB.Net–a percentage that has remained constant since the introduction of VB.Net at the end of 2002″
Also, DDOE had a post about the subject the other day.
(via the del.ico.us feed of Nathan Slaughter)
22 different VBA examples for selecting: How to select cells/ranges by using Visual Basic procedures in Excel
I just read on Andrew Engwirda’s site they have created a new Excel forum: Microsoft Excel QA Board
If you need an Excel question answered or like answering Excel questions, I’d go check it out.
“it’s run by Colo aka Masaru Kaji (from Colo’s Excel Junk Room), Gareth Lombard (from The Excel Maniacs) and starting from yesterday, myself also.(Andrew)”
PHP Freaks has a great article on exporting a MySQL table to an Excel spreadsheet. Even better, unless you plan on learning what the code does, there is no reason to read the entire article. Here’s what to do to get the export working in 3 steps:
1. Go to the last page and download the ZIP file (halfway down the page in the center)
2. Modify the PHP file you just downloaded with your database login info, and change the “Select Query” to your table name to export
3. Upload it to your server and navigate or link to the newly uploaded Excel Export PHP page.
The following is an example to export the Stats table my blogging software creates to log visitors to this site. Depending on when you stumble on this post, the file may be large or small (I empty it every few weeks).
Click this link to download my Stats table to Excel.
Let me know if it doesn’t work for you, I’ve tested it in IE only. I read some people say you need to have Excel opened before clicking the link, but this isn’t the case so far.
Tom | 
03 31st, 2005| 
