Hello,
I am trying to make a Iwork Numbers file where i have a sheet where i paste my bank statements
from my online bank into numbers, an then another sheet that contains all the store i usually shop in.
I then identify each store and divide the statements into categories like, Food, Clothes, House etc.
This is off course quite tedious work, so i want to automate this.
I want to make a applescript that takes each cell in the “Stores” sheet, and checks it against each
cell in the bankstatement sheet. And when it find anything, then marks a corresponding cell with the categori.
I have made a numbers file that does almost everything now, apart from the cell checking, which i now want
to do in numbers.
My REALLY big problem, is that i am a total newbie with applescripting, so i wanted to ask you if anyone
could help me with the checking script.
I have made a file, so you can see what i am trying to do.
http://www.anacropolis.dk/numbers/Bankstatements.numbers
I hope it makes sense, and that you will be able to help me.
Help
You are the only one knowing the way to download dates from your bank.
I guess that if you enter my iDisk : http://public.me.com/koenigyvan,
mainly the folder :
For_iWork:iWork '09:for_Numbers09:
You will find useful infos in the numerous scripts available.
two of them may be really useful :
For_iWork:iWork '09:for_Numbers09:insert_quotes.zip
For_iWork:iWork '09:for_Numbers09:insert_Currencies_rates.zip
CAUTION: my scripts were designed for Numbers as a component of the iWork package.
I know that the one delivered thru the mac App Store isn’t installed the same way than the first one so, some handlers may require some changes.
At this time I don’t know the exact implantation of resources in this new release and for sure, I will not buy it just for see.
Yvan KOENIG (VALLAURIS, France) mercredi 25 mai 2011 16:21:00
Hello,
And thank you for your reply. I think i might not have made myself totally clear.
I do not want to download dates and bankstatements from the web bank.
I have all the data in my sheet already, and i only want a script that can search for store names
in the “info” cells already in the sheet. I hope this makes it easier?
Best…Jan
It seems you want the script to format the cells for you too. 
There doesn’t appear to be any way in Numbers’s scripting dictionary to get bold text or to specify Danish currency. The script below uses System Events to perform a Command-“b” keystroke to embolden the entries in your “OK/Store not found” column and assumes that currency will default to Danish on your machine anyway. Hopefully it’ll get you started.
-- Test the layout of the "Bank statement" spreadsheet and do some cell formatting in it.
on setup()
tell application "Numbers"
activate
tell sheet "Bank statement" of document 1
tell table "Bank statements"
-- Identify the headers row and th "Info", "Amount", and "OK/Store not found" columns.
set {column:infoColumn, row:headerRow} to first cell whose value begins with "Info"
set amountColumnNo to address of column of first cell whose value begins with "Amount"
set okColumn to column of first cell whose value begins with "OK/"
set {name:okColumnLetter, address:okColumnNo} to okColumn
-- Identify the category columns and their headers.
set {name:firstCatColumnLetter, address:firstCatColumnNo} to column (okColumnNo + 1)
set {name:lastCatColumnLetter, address:lastCatColumnNo} to column of last cell of headerRow whose value is not 0.0
set categoryHeaders to value of cells firstCatColumnNo thru lastCatColumnNo of headerRow
-- Identify the beginning and end rows of the statement data.
set startRow to (address of headerRow) + 1
set endRow to address of row of last cell of infoColumn whose value is not 0.0
-- Format the "OK/Store not found" column for black, centred text.
set okRangeName to (okColumnLetter & startRow & ":" & okColumnLetter & endRow)
set text color of range okRangeName to {0, 0, 0}
set alignment of range okRangeName to center
-- Format the category columns as currency.
set format of range (firstCatColumnLetter & startRow & ":" & lastCatColumnLetter & endRow) to currency
end tell
end tell
end tell
-- Return useful data to the main handler.
return {infoColumn:infoColumn, amountColumnNo:amountColumnNo, okColumnNo:okColumnNo, okRangeName:okRangeName, categoryHeaders:categoryHeaders, startRow:startRow, endRow:endRow}
end setup
on main()
set {infoColumn:infoColumn, amountColumnNo:amountColumnNo, okColumnNo:okColumnNo, okRangeName:okRangeName, categoryHeaders:categoryHeaders, startRow:startRow, endRow:endRow} to setup()
tell application "Numbers"
tell sheet "Bank statement" of document 1
-- Get the data from the "Stores" spreadsheet.
tell table "Stores"
-- This assumes that the two columns have matching entries in each row.
set storeNames to value of every cell of column 3 whose value is not 0.0
set categories to value of every cell of column 4 whose value is not 0.0
end tell
tell table "Bank statements"
repeat with i from 1 to (count storeNames)
-- For each store name and its matching category .
set {thisName, thisCategory} to {item i of storeNames, item i of categories}
-- . identify the number of the column matching the category .
repeat with i from 1 to (count categoryHeaders)
if (thisCategory is item i of categoryHeaders) then
set catColumnNo to i + okColumnNo
exit repeat
end if
end repeat
-- . and the rows containing payments to the store.
set matchingRows to (row of cells of infoColumn whose value contains thisName)
-- Set the category and OK cells in those row as appropriate.
repeat with thisRow in matchingRows
tell thisRow
set value of cell catColumnNo to value of cell amountColumnNo
set value of cell okColumnNo to "OK"
end tell
end repeat
end repeat
-- When done, fill the unfilled cells in the "OK" column with "Store not found" in red.
tell range okRangeName
tell (cells whose value is not "OK")
set text color to {65535, 10023, 4626}
set value to "Store not found"
end tell
end tell
-- There's no apparent way to set bold text in Numbers, so set the selection to the cells in the "OK" column to prepare for a Command-"b" keystroke.
if (selection range exists) then
set oldSelectionRange to name of selection range
else
set oldSelectionRange to missing value
end if
set selection range to range okRangeName
end tell
end tell
end tell
-- Perform the Command-"b" keystroke to bolden the entries in the "OK" column and restore the Numbers selection to what it was (_if_ it was).
tell application "System Events" to keystroke "b" using {command down}
if (oldSelectionRange is not missing value) then tell application "Numbers" to tell table "Bank statements" of sheet "Bank statement" of document 1 to set selection range to range oldSelectionRange
end main
main()
Sorry for my late reply.
Thank you very much this is awesome!!!
Best…Jan