Numbers Applescript

fearbabyriffs · August 29, 2011, 7:53am

Is it possible to do this?

I have everyday 2 csv files that I need to figure a simple composite score in Numbers via applescript) that outputs new re-sorted 3rd Numbers sheet by new composite score.)

Every stock symbol has known score in the 1st csv:
(Following are already scored 1-12 usually 6000 different stock symbols)

“No.”,“Ticker”,“Company”,“Sector”,“Industry”,“Country”,“Market Cap”,“P/E”,“Price”,“Change”,“Volume”
1,“BPT”,“BP Prudhoe Bay Royalty Trust”,“Basic Materials”,“Oil & Gas Refining & Marketing”,“USA”,2339.66,12.26,109.33,0.95%,75598
2,“PBT”,“Permian Basin Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,1026.35,15.96,22.02,1.80%,190976
3,“SBR”,“Sabine Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,885.01,15.93,60.70,1.08%,9630
4,“MSB”,“Mesabi Trust”,“Financial”,“Diversified Investments”,“USA”,328.79,10.71,25.06,3.13%,67290
5,“NRT”,“North European Oil Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,301.16,14.19,32.77,0.77%,14990
6,“SJT”,“San Juan Basin Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,1130.29,17.20,24.25,1.63%,81551
7,“MARPS”,“Marine Petroleum Trust”,“Financial”,“Diversified Investments”,“USA”,44.00,14.86,22.00,2.37%,3514
8,“PCO”,“Pendrell Corporation”,“Technology”,“Wireless Communications”,“USA”,615.61,1.99,2.37,3.49%,283468
9,“TNH”,“Terra Nitrogen Company, L.P.”,“Basic Materials”,“Agricultural Chemicals”,“USA”,3297.44,15.42,178.24,2.18%,61131
10,“GNI”,“Great Northern Iron Ore Properties”,“Basic Materials”,“Steel & Iron”,“USA”,158.46,7.60,105.64,-0.61%,2850
11,“ISIG”,“Insignia Systems Inc.”,“Services”,“Marketing Services”,“USA”,49.48,0.88,3.23,3.53%,46293
12,“ZIXI”,“Zix Corp.”,“Technology”,“Application Software”,“USA”,203.17,4.95,3.17,2.92%,307800

2nd csv has the stock symbols ranked possibly like this on a random day:

“No.”,“Ticker”,“Company”,“Sector”,“Industry”,“Country”,“Market Cap”,“P/E”,“Price”,“Change”,“Volume”
1,“ZIXI”,“Zix Corp.”,“Technology”,“Application Software”,“USA”,203.17,4.95,3.17,2.92%,307800
2,“SBR”,“Sabine Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,885.01,15.93,60.70,1.08%,9630
3,“PBT”,“Permian Basin Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,1026.35,15.96,22.02,1.80%,190976
4,“MSB”,“Mesabi Trust”,“Financial”,“Diversified Investments”,“USA”,328.79,10.71,25.06,3.13%,67290
5,“BPT”,“BP Prudhoe Bay Royalty Trust”,“Basic Materials”,“Oil & Gas Refining & Marketing”,“USA”,2339.66,12.26,109.33,0.95%,75598
6,“MARPS”,“Marine Petroleum Trust”,“Financial”,“Diversified Investments”,“USA”,44.00,14.86,22.00,2.37%,3514
7,“SJT”,“San Juan Basin Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,1130.29,17.20,24.25,1.63%,81551
8,“PCO”,“Pendrell Corporation”,“Technology”,“Wireless Communications”,“USA”,615.61,1.99,2.37,3.49%,283468
9,“NRT”,“North European Oil Royalty Trust”,“Financial”,“Diversified Investments”,“USA”,301.16,14.19,32.77,0.77%,14990
10,“GNI”,“Great Northern Iron Ore Properties”,“Basic Materials”,“Steel & Iron”,“USA”,158.46,7.60,105.64,-0.61%,2850
11,“ISIG”,“Insignia Systems Inc.”,“Services”,“Marketing Services”,“USA”,49.48,0.88,3.23,3.53%,46293
12,“TNH”,“Terra Nitrogen Company, L.P.”,“Basic Materials”,“Agricultural Chemicals”,“USA”,3297.44,15.42,178.24,2.18%,61131

What I need is:

Stock Symbols each get a new composite score:

So BPT for example would get a score of 6 (1+5)
ISIG would get a score of 22 (11+11) and so on.
TNH would get a score of 21 (9+12)

All rows with “Financial” or “Utilities” are stripped out.
Newly composite scored stock symbols are re-sorted ASCENDING
Outputted into a 3rd Numbers (sheet?) with Composite Ranks in the 1st column.

Thanks for taking a look, as I have no idea how to start this.

Model: Macbook Pro
Browser: Firefox 5.0.1
Operating System: Mac OS X (10.6)

Nigel_Garvey · August 29, 2011, 1:37pm

This may get you started:

-- Customisable insertion sort.
on CustomInsertionSort(theList, l, r, customiser)
	script o
		property comparer : me
		property slave : me
		property lst : theList
		
		on isrt(l, r)
			set u to item l of o's lst -- The highest value sorted so far!
			repeat with j from (l + 1) to r
				set v to item j of o's lst
				if (comparer's isGreater(u, v)) then
					set here to l
					set item j of o's lst to u
					repeat with i from (j - 2) to l by -1
						tell item i of o's lst
							if (comparer's isGreater(it, v)) then
								set item (i + 1) of o's lst to it
							else
								set here to i + 1
								exit repeat
							end if
						end tell
					end repeat
					set item here of o's lst to v
					slave's shift(here, j)
				else
					set u to v
				end if
			end repeat
		end isrt
		
		on isGreater(a, b)
			(a > b)
		end isGreater
		
		on shift(a, b)
		end shift
	end script
	
	set listLen to (count theList)
	if (listLen > 1) then
		if (l < 0) then set l to listLen + l + 1
		if (r < 0) then set r to listLen + r + 1
		if (l > r) then set {l, r} to {r, l}
		
		if (customiser's class is record) then set {comparer:o's comparer, slave:o's slave} to (customiser & {comparer:o, slave:o})
		
		o's isrt(l, r)
	end if
	
	return -- nothing.
end CustomInsertionSort

-- Customising script object for the above sort. (Sort a list of lists by item i of each list.)
script sortOnItem
	property i : missing value
	
	on isGreater(a, b)
		(item i of a > item i of b)
	end isGreater
end script

-- The main business.
on main()
	-- Script object for speed of access to three lists: the lines of the two csv files and the start of the data for a third.
	script o
		property csv1 : paragraphs of (read (choose file) as «class utf8»)
		property csv2 : paragraphs of (read (choose file) as «class utf8»)
		property csv3 : {item 1 of csv1} -- The headers.
	end script
	
	set astid to AppleScript's text item delimiters
	
	-- Break every line of both sets of data (bar the headers) into lists of text items using the comma delimiter.
	set AppleScript's text item delimiters to ","
	set lineCount to (count o's csv1)
	repeat with i from 2 to lineCount
		set item i of o's csv1 to text items of item i of o's csv1
		set item i of o's csv2 to text items of item i of o's csv2
	end repeat
	
	-- For convenience, sort lines 2 thru -1 of both csv1 and csv2 on their second ("Ticker") items, so that they're in a corresponding order.
	set sortOnItem's i to 2
	CustomInsertionSort(o's csv1, 2, -1, {comparer:sortOnItem})
	CustomInsertionSort(o's csv2, 2, -1, {comparer:sortOnItem})
	
	-- For every line of csv1 except the headers,
	-- if the line doesn't contain either "Financial" or "Utilities",
	-- set the first item of the line to the sum of it and the first item from the corresponding line in csv2
	-- and append the line to csv3.
	repeat with i from 2 to lineCount
		set thisLine to item i of o's csv1
		if not ((thisLine contains {"Financial"}) or (thisLine contains {"Utilities"})) then
			set item 1 of thisLine to (item 1 of thisLine) + (item 1 of item i of o's csv2)
			set end of o's csv3 to thisLine
		end if
	end repeat
	
	-- Sort lines 2 thru -1 of csv3 on their first ("No.") items.
	set sortOnItem's i to 1
	CustomInsertionSort(o's csv3, 2, -1, {comparer:sortOnItem})
	
	-- Coerce csv3's lists of text items to comma-delimited lines.
	repeat with i from 2 to lineCount
		set item i of o's csv3 to item i of o's csv3 as text
	end repeat
	
	-- Coerce the lines to a single text.
	set AppleScript's text item delimiters to linefeed
	set o's csv3 to o's csv3 as text
	
	set AppleScript's text item delimiters to astid
	
	-- Write the text to a temporary CSV file.
	set csv3Path to (path to temporary items from local domain as text) & "CSV3.csv"
	set fref to (open for access file csv3Path with write permission)
	try
		set eof fref to 0
		write o's csv3 as «class utf8» to fref
	end try
	close access fref
	
	-- Open the file in Numbers.
	tell application "Numbers"
		launch
		activate
		open file csv3Path
	end tell
end main

main()