Have you ever wanted to sort or filter by the background color of cells in Excel? There are no functions built into Excel to do such a thing, but you can still do it with this simple user defined function:

Function Color(MyRef As Range) As Long
Color = MyRef.Interior.ColorIndex
End Function


I found installing it a bit convoluted (at least in Excel 2003). First you want to make sure you get it into your personal workbook so it will always be available to you. But when I went to the Visual Basic editor in Excel, it wouldn’t show me my personal workbook.

What I did was record a simple macro in Excel, being sure to select to store it in the “personal macro workbook” option. Do something simple like type in a cell, and hit stop recording. Then go into the Visual Basic editor again, and you can see your personal workbook (PERSONAL.XLS). Place the function above in the same module as the macro you just recorded. You can delete the macro you recorded now.

To use the function you have to call it in a funny way. Thus to get the color of cell A3 you would type in a cell:

=PERSONAL.XLS!Color(A3)

And now you can get numeric values for the background colors in your cells! Yay!

Posted by Greg Pinero (Primary Searcher) as Excel at 4:45 PM MST

1 Comment »

When saving an Excel file as DBF 4, I was finding that the values in one column were getting truncated.

Well it turns out that you have to change the width of your columns to avoid this behavior. So make sure every column width is wide enough to show the full text from that column before saving as DBF. (Even make it wider than auto width to be safe.)

Do note that when saving in DBF 4 format from excel, it will still insist on cutting off your header (first row) values at 10 characters.

Posted by Greg Pinero (Primary Searcher) as Excel, GoldMine at 12:37 PM MST

Comments Off

Have you ever wanted to fit a line to a set of data points using Python? Well, if you ever do, here’s how do to it:

Method 1:
The NumPy/SciPy package has a built in function; stats.linregress you can use. I didn’t choose this method because I didn’t want to fool with installing a package for my simple one-off need. There’s an example of using SciPy’s linear regression function here.

Method 2:
I found and modified the linreg function on this helpful website: Simple Recipes in Python by William Park.
Read the rest of this entry »

Posted by Greg Pinero (Primary Searcher) as Python, Excel at 2:56 PM MST

3 Comments »

It turns out that this nifty pyExcelerator program will let you write an Excel formatted document directly from Python.

While you could always use COM automation with the Python Win32 extensions to make Excel documents, I think this library is the way to go. It lets you write an Excel file without even having to have Excel installed, and you don’t even need to be running on Windows. That’s pretty neat. It sounds like an easy way to offer an Excel output format option from your application or website, without requiring any messing with Windows or Excel.

Sadly there wasn’t any friendly documentation for this project online, so here I offer my first code snippet using this package. I mostly figured it out from the example files included with the package. It writes a few rows of data and styles the data depending on content.
Read the rest of this entry »

Posted by Greg Pinero (Primary Searcher) as Python, Excel at 3:47 PM MST

17 Comments »

Here’s the syntax to write to a CSV file:

import csv
w=csv.writer(file(r'C:greg.csv','wb'),dialect='excel')
some_values=[[1,2,3],['A','B','C'],[4,'"5"','ab,c']]
w.writerows(some_values)

Make sure you use ‘wb’ when you open the file. Otherwise I’ve found that it will put a blank line between each row at least when Excel opens it.

Here’s the syntax for reading a csv file (from the documentation):

import csv
reader = csv.reader(open("some.csv", "rb"))
for row in reader:
    print row[0], row[-1]

And here’s the link to the full documentation:
http://docs.python.org/lib/module-csv.html
And this takes you straight to the examples in the documentation.

Update:
Another useful writeup of the CSV module.

Tags: Python, csv, csv file, comma delimited, comma seperated values, comma seperated, Python documentation

Posted by Greg Pinero (Primary Searcher) as Python, Excel at 3:20 PM MST

Comments Off