I have a three lists of data - two ordinates and an abcissa - that I’d like to plot as a simple two-line graph. I do have Excel X (not 2004), but I understand that scripting it is an exercise in frustration (haven’t tried). Smile, Chartsmith, and several other scriptable graphics programs are rather expensive for this one-shot exercise. Is there an inexpensive shareware alternative?
So you haven’t tried, and are dismissing it? Hmm.
How do you know it won’t do what you need it to do without much headache if you haven’t taken a shot?
I’m unsure if I even understood the question, but maybe this might be useful?
http://www.petermcm.dircon.co.uk/software/og_line_graph/index.html
This person has written a bunch of scripts for creating line/bar/pie charts and assorted shapes in OmniGraffle. I didn’t see a two-line graph script. Maybe you could fiddle with the one-line graph script to give you what you needed.
hi Adam,
if you are looking for a good spreadsheet program, Neo Office looks kind of cool. i haven’t used it, but it is based on Open Office from Sun, and is reportedly compatible with Excel documents.
NOTE: i have not used this myself.
Mikey; Of course I intend to try -
I just wondered if there was neat, simple 2D grapher that was scriptable or that could at least be cajoled into opening a comma or tab delimited text document. The dictionary in Excel X looks like a rough translation of MS’s VBA, which I don’t know. It’s in 2004 that MS made Excel very scriptable and I’m not willing to upgrade for this project.
I’m extracting messages from a long series of daily weather emails, parsing for the data I want into three lists (date, high, low), doing some data smoothing by keeping a running average, coercing the lists to a tab/return delimited table, and plotting the result. Everything works now except the ‘plotting the result’ which I’ve been doing by dragging the doc to Excel and charting the table by hand, and I’d like to refine my running average a bit.
Each day’s forcast (the email) also predicts long range, so eventually, I’d like to compare the forecasts to the actual data for each of those days and show that somehow too.
I’m just amusing myself.
Soj;
Thanks for the pointer to OG - I don’t have OmniGraffle, however.
Waltr;
Thanks for the link to Neo. As far as I can tell, however, it isn’t scriptable.
Back to deciphering Excel X’s dictionary for scaling a chart given a tab-delimited text document (3 columns by 590 rows).
Thanks all.
Hi Adam, may help some help you if you post a sample one of those docs.
Sure, Mark. Here’s a piece of one (tab delimited):
8/4/06 16.74245993416 23.380909989703
8/5/06 16.928940850731 23.706204141065
8/6/06 16.560207180129 23.876444632963
8/7/06 16.307901991255 23.490889690933
8/8/06 16.699262505529 23.01838461624
8/9/06 16.391842919228 23.346429735034
8/10/06 16.099751854003 23.463926010552
8/11/06 14.79771431969 22.207185090456
8/12/06 14.32232727323 21.75438520901
8/13/06 14.304948361731 21.856374077505
8/14/06 14.606247488663 22.204486094243
8/15/06 14.808380780906 21.703811345189
8/16/06 14.179894157825 22.441167879234
8/17/06 14.648630606848 22.587366329667
8/18/06 14.909226386395 22.933086388523
8/19/06 14.434437787767 22.990405149356
8/20/06 14.248073695253 21.627714466886
8/21/06 14.897934467354 22.387801501191
8/22/06 14.395111487593 23.251480279358
8/23/06 14.63527991255 22.316749061859
8/24/06 13.482081466874 21.989007710602
8/25/06 13.128118216754 22.139309262955
8/26/06 13.811369899045 21.361266508854
8/27/06 13.605392395668 21.372395870603
8/28/06 13.636220127867 20.718242910603
8/29/06 14.763245605645 20.612976322515
8/30/06 13.751214532295 21.17590377593
8/31/06 14.037670066452 20.376780752262
9/1/06 13.138032551098 20.041415212677
Had a quick look not tried anything, but in my exel (2004 ) It looks like I can setup a custom chart.
In the Dictionary for exel,
it mentions
chart wizard‚v : Modifies the properties of the given chart.You can use this method toquickly format a chart without setting all the individual properties. This method is noninteractive, and it changes only the specified properties.
chart wizard chart
[source range] : The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.
[gallery column clustered/column stacked/column stacked 100/ThreeD column clustered/ThreeD column stacked/ThreeD column stacked 100/bar clustered/bar stacked/bar stacked 100/ThreeD bar clustered/ThreeD bar stacked/ThreeD bar stacked 100/line stacked/line stacked 100/line markers/line markers stacked/line markers stacked 100/pie of pie/pie exploded/ThreeD pie exploded/bar of pie/xy scatter smooth/xy scatter smooth no markers/xy scatter lines/xy scatter lines no markers/are…
I think if your version has this it may be what your looking for.
Thanks. When I’m through tuning up my ‘smoother’, I’ll have a look at that. 
You probably can do this better, but this is new to me.
this get it into My excel,
got to figure out how to format cells to 11 decimal places. in the script and the rest…
set sdates to do shell script "grep '\\/' /Users/UserName/Desktop/weather.txt | awk -F' ' '{print $1}'"
set sHigh to do shell script "grep '\\/' /Users/UserName/Desktop/weather.txt | awk -F' ' '{print $2}'"
set sLow to do shell script "grep '\\/' /Users/UserName/Desktop/weather.txt | awk -F' ' '{print $3}'"
set para to count paragraphs in sdates
tell application "Microsoft Excel"
--activate
make new document
set horizontal alignment of range "A:A" to horizontal align right
set horizontal alignment of range "B:B" to horizontal align right
set horizontal alignment of range "C:C" to horizontal align right
set column width of range "A:A" to 15
set column width of range "B:B" to 20
set column width of range "C:C" to 20
end tell
set counter to 0
repeat para times
set counter to counter + 1
tell application "Microsoft Excel"
set value of cell ("$A$" & counter as string) to paragraph counter of sdates
set value of cell ("$B$" & counter as string) to paragraph counter of sHigh
set value of cell ("$C$" & counter as string) to paragraph counter of sLow
end tell
end repeat
Depending on your needs, you can use Smile (free edition). QuickCurve (graphic engine) is very easy to use -you can’t print/save as PDF in the un-registered version, though, but you can run your script every time you wish, and even print a screenshot.
And the PDF engine is free. So you can create your own line-based graphic using easy “commands”, as “lineTo” or “curveTo”.
Both the Smile distribution and the Satimage site are full of examples
Thanks, jj;
I didn’t look into that, and I don’t need a printed record - this is basically just a study.
A
Then, here is a sample with QuickCurve():
set theDates to {"8/17/06", "8/23/06", "8/25/06", "8/27/06", "8/29/06", "9/1/06"}
set nums1 to {14.1, 15.2, 13.7, 14.4, 16.7, 15.1}
set nums2 to {20.04, 21.2, 23.5, 20.7, 22, 21.8}
--> define x coordinate for all
set initDate to date (item 1 of theDates)
set ddates to {}
repeat with i from 1 to count theDates
set ddates's end to ((date (theDates's item i)) - initDate) / 86400
end repeat
--> define y and draw curves for every "num"
tell application "Smile"
set pw to (item 3 of (get screen bounds)) - 60
set ph to (item 4 of (get screen bounds)) - 100
set v to (make new graphic window with properties {name:"Curves graph", pageheight:ph, pagewidth:pw, visible:false})
set v's bounds to {0, 0, pw + 60, ph + 100}
set c to QuickCurve(ddates, nums1, v)
set name of c to "nums1"
set v to container of c
set c to QuickCurve(ddates, nums2, v)
set name of c to "nums2"
set properties of v to {name:"Some numbers", legend abscissa:((count ddates) / 2), legend kind:2, xlabel:"days since " & (initDate as text), ylabel:"nums", legend frame:{0, 0, 50, 12}, legend sample length:10}
activate
draw window of v
set visible of window of v to true
set never save of window of v to true
end tell
And a minimalist example with the graphic engine:
set nums1 to {14.1, 15.2, 13.7, 14.4, 16.7, 15.1}
set nums2 to {20.04, 21.2, 23.5, 20.7, 22, 21.8}
set z1 to {}
set z2 to {}
repeat with i from 1 to count nums1
set z1's end to {20 * i, (nums1's item i) * 2}
set z2's end to {20 * i, (nums2's item i) * 2}
end repeat
BeginFigure(0)
SetPenWidth(3)
SetPenColor({0, 0, 0})
PolyLine(z1)
DrawPath(2)
SetPenColor({1, 0, 0})
PolyLine(z2)
DrawPath(2)
EndFigure()
Thanks again, jj. I couldn’t get the QuickCurve version to compile, but I’ll look into my Smile stuff and see whether I’ve got what’s required. I’ve never used Smile because I never sorted out how it works; somehow it seemed unnatural compared to the Script Editor and Script Debugger 4 - Using Smile requires understanding how it works (su ‘paradigma’), and I never got that far with my initial trials. SD4 does what I expect it to do.
Maybe it requires Smile “full edition” (which includes SmileLab). It’s just as the “regular edition”, but including all the stuff in the SmileLab suite (including, I guess QuickCurve and more things). You won’t be able to save/print stuff created using SmileLab, but you can still run the given example
And yes, I agree. The advanced features in Smile are not a trivial thing, but the more you learn, the more you enjoy. What I love mainly in Smile is its absolute attachability!
But Excel is recordable. While not perfect, and if you avoid the “wizards,” it does work. I’ve used the recordable functionality to at least decipher 90%-finished solutions, then fine-tune. Or to ferret-out odd syntax and property names.
Another trick I’ve used once is to record a macro. This dumps you into the VB editor (looks like VB anyway). The VB syntax and properties are generally the same as AppleScript so sometimes you can use this to pry additional syntax out of Excel (painful as doing it this way can be).
Thanks, Calvin - that’s a VERY useful suggestion. It didn’t occur to me to try to record what I wanted.
I used to be a fiend for Excel macros back when Excel used its original macro language (which still seems to work). I had reams of them for preparing grade sheets (I was a prof), graphs, solving and graphing engineering problems, even doing some simple digital signal processing work. My need for those stopped when I retired, so I’ve not kept up.
I wish more apps were recordable…I learned more about scripting Excel by recording what I wanted to do and breaking-apart the resultant recording than I ever did poking around in the Dictionary. One thing I quickly discovered is Excel is much more scriptable than the dictionary alludes to. The AppleScript Dictionary seems to leave out many, many properties that are available.
The only gotcha I’d warn you about again is that the “wizards” and other “one step” ways to shortcut around common tasks do not appear to be very scriptable, or their scriptable command set are out of sync with the functions of the wizard, or don’t behave the same way. I got into alot of trouble trying to script the ChartWizard for example. Found it easier to have AppleScript do everything “the long way.”
Another charting tip:
I had a chart that I wanted to “append” on a regular basis via automation. I thought the easiest way was to regenerate the chart every time. But scripting the Wizard made my head hurt. So it turned-out to be easier to make the chart manually once, save it, and script modifications to that chart: