Transfer from Apple Numbers To Quicken Macro (v9.2)

[BernSh]

This is just a take on accomplishing this. Of course, other thinking is most welcomed!

Issue:
Quicken not retrieved all account data. Missing transactions must be entered manually.

Outcome:
Data from Apple Numbers (v10.3.9) 3 column Spreadsheet is used to create new transactions in Quicken (v6.0.3) on MacOS 11.1.

Background:
Readable PDF statements downloaded from Bank of America.
PDF converted to Spreadsheet using Abbyy FineReader.
Spreadsheet edited down to 3 columns - Date/Payee/Amount.
Not every row needs to be brought over. I need to compare and only bring over missing transactions.

Considerations:
Quicken 2021 seems to have no APIs to facilitate data entry, so this needs to be UI scripted.

I need distinct help with the date entry in that month/day/year are separated by tabs within the Date column in Quicken.

Plan:
Place Spreadsheet on the left half of the screen and Quicken on the right half and use the macro to transfer needed data to Quicken.

Steps 1a-c
The cursor moves to absolute position, clicks, and copies the first date from the first column of Spreadsheet that formatted xx/xx/xx and copies it to Named Clipboard ‘Date’.

Type Tab moves focus to second column and copies to Named Clipboard ‘Payee’.

Type Tab moves focus to third column and copies to Named Clipboard ‘Amount’.

Step 2
Activate Quicken.

Step 3
Type ⌘N to create new transaction.

Step 4
Outcome: Paste Clipboard ‘Date’ into date column.

---------- Help Needed ----------

Maybe split date formatted xx/xx/xx into three parts separated by tabs or into three new separate named clipboards or filter and extract first two characters via regex?

Step 4a
When a new transaction is created in Quicken, the month date field is selected, so paste the first two characters from the new month clipboard or regex-ed month part of the date extracted. Then do the same for the Day and Year parts of the date.

Step 5
Type tab

Step 6
Paste Clipboard 'Payee' into Payee column

Step 7
Type tab

Step 8
Paste Clipboard 'Amount' into Amount column

Step 9
Activate Numbers and manually move to the next row to be copied into position for Step 1a.

Current Macro: (with a missing tab for Step 7)
Transfer from Numbers Macro (v9.2)
Transfer from Numbers.kmmacros (6.4 KB)

Research:
This first one is closest on the forum but is for Quicken 2017, which is substantially different than the current subscription version as far as I know.

How Do I Update Quicken 2017 from a Variety of Sources, including Apple Numbers?

Background: Have to use two financial software packages. One copies fields from a list of transactions in a Numbers spreadsheet. The second macro, posted here, enters most of the transaction in Quicken. History: My macros worked great for several months. I’m now having problems with copying and pasting. Copy function (not uploaded) Have tried Command C, and copy functions. Not always reliable. Helped when I inserted pauses between each action, pulls the data 90% of the time now. As part…

Anyway to organize clipboards into a table ready to paste into excel/sheets?

In a current process I'll have 20+ different named clipboards and the macro will paste each value cell by cell, Example actions: Paste from named 1, Tab, Paste from named 2, Tab, Paste from named 3, Tab, Return to start of next row, Paste from named 4, Tab, etc. etc. I was curious if there was anyway to organize the named clipboards into a table internally so everything could be pasted all at once.

Paste In Order Copied

Use Case Sometimes you need to copy things (text & graphics) in the same order as you want to paste them. It is a big hassle to copy one item, switch to the target destination, paste, then switch back, ad infinitum. So this pair of macros lets you copy everything at once. Then, when done, switch to the destination, and paste in order, all at once, or one at a time. And, you can select from one a several common separators, or roll-your-own. Check out the video demos below. ==U…

How Do I Copy Cells From Excel and Paste Contents of Each Cell on a Separate Line?

I'm trying to make this work but it doesn't at all. I'm copying a bunch of cells in Excel, all on the same column. I've tried with Peter's regex and carycrusiau's but neither of those work. I was using a For each line before but now I need to be able to paste those lines one by one so I don't know how to step through those semi-automatically. Here's a link to my previous poste. So how can I modify my previous macro to let me paste manually for each line, or how do I modify the grep so it works…

Customize clipboard contents and then paste

So I want to test user database, when I copy them they are copied in this format username:password what I want to achieve with KM is: Paste the username in the clipboard that is before ( colon and hit tab key Paste the password that is after that was in the clipboard ( colon and press enter How can I achieve this any ideas?

Find & replace inside clipboard text, each time I press cmd+c

Here's my wish: Everytime I command+c any text (mostly a windows paths that contains backslashes), I wish the slashes automatically get changed to normal /-slashes. So X:\whatever\stuff\path I'll just select "\whatever\stuff\path", press command+c and I want to paste "/whatever/stuff/path" How do I do that?

[ccstone]

@BernSh

Data from Apple Numbers (v10.3.9) 3 column Spreadsheet is used to create new transactions in Quicken (v6.0.3) on MacOS 11.1.

Hey Bern,

I dislike Apple Numbers intensely – every time I fiddle with it I end up pulling my hair out and going back to Excel.

I was just able to get Number 10 for my Mojave MacBook Air, so I'll take a look at where AppleScript has gotten to in it.

But in the meantime – my advice offhand is to manually select the range you need in the spreadsheet and then copy to the clipboard.

You should get neat, clean tab-delimited text records for each row.

From there processing with Keyboard Maestro is easy – or at least pretty straightforward.

Using the Lines In collection action you can iterate through the lines in the clipboard – separate the tab-delimited lines into their necessary components – and get on with life.

-Chris

[BernSh]

@ccstone

I dislike Apple Numbers intensely – every time I fiddle with it, I end up pulling my hair out and going back to Excel.

Ahhh, straightforwardness is refreshing. Bless you!

I’ve used Excel and Numbers for decades and found each better for different things. I’d gladly keep Excel around, except I intensely dislike paying MS a monthly fee for a tool I don’t have that frequent use for. It’s a waste for me. This is a remedial repair of my Quicken file and while a tiresome slog, once done, the need will hopefully not reappear. Freebie Numbers satisfies my SS needs. This is a great kick in the butt to keep Quicken up to date.

Sorry for being such a dull date both by needing so much hand-holding while asking you to deal with software that is such a drag to script. Thanks for having the commitment to contribute hold sway.

Here's where I need help.

I can get the row selected and copied to the system clipboard. Activate/Click/Copy then what is copied is on the System Clipboard. Just my speed!

I have the Lines in a Collection version of a For Each action all saddled up!

Here's where I get stopped. I can't find an "iterate through the lines in the clipboard – separate the tab-delimited lines into their necessary components" action even within the entire list of actions.

Seriously, would you either show me the actions needed to do this or, probably more work, give me the thinking to bring to the list of actions so I can pick and figure this out?

I understand/guess that the For Each action will magically 'read' the characters on the system clipboard and recognize the tab characters and thereby perform a further action (the execute the following actions next part) on each now somehow discrete parts of that string.

I do not know how to think about, do not have any distinction to bring to the next step. I don't have a programmer's mindset. I get I can do something with these now discrete groups of characters, and I'm guessing that I want to move each section to a separate clipboard, or at least that was the direction I was heading in originally.

I see a 'copy to named clipboard' action that would move the whole shebang from the system clipboard to a named clipboard but does not separate the tab-delimited string into its parts.

I'd be glad to get at least some automation going and forgo automating the date fields and just get the Amount and Payee clipboards loaded from the system clipboard unless you see a better way.

Transfer from Numbers Macro (v9.2)

Transfer from Numbers.kmmacros (7.8 KB)

[JMichaelTX]

@BernSh

Seriously, would you either show me the actions needed to do this or, probably more work, give me the thinking to bring to the list of actions so I can pick and figure this out?

@BernSh, may I request that in the future that you lead with your specific request?
I almost missed it because, first, I didn't want to read a long post, and then second it was buried in the middle of your post.

So, within the For Each of each line, you need to extract the data between the TABS.
I would probably do this using a KM Search using Regular Expression action.

Let's say there are three values separated by TABS.

So I'd use:
([^\t]+)\t([^\t]+)\t([^\t]+)

which capture all characters EXCEPT TABS between the TABS,
saving each Capture Group into KM Variables, named for their contents.
I'm pretty sure if you do a search in the forum on "Excel" you will find some examples.

[ccstone]

Hey Bern,

For this sort of thing it's a good idea to post a dummy version of your spreadsheet with some real-world data. Fake data is fine, as long as it's real-world and not just meaningless placeholders. You have no idea how much trouble programmers go through because of incomplete descriptions of requirements – or for that matter how hard it is to completely describe data in prose.

Nevertheless – looking at the image of your spreadsheet it appears there are only 3 columns, and that's not overly hard to handle.

@BernSh

Here's where I need help.

I can get the row selected and copied to the system clipboard. Activate/Click/Copy then what is copied is on the System Clipboard. Just my speed!

First and foremost I'm not suggesting you go row-by-row in your spreadsheet.

I'm suggesting that you select the ENTIRE range of rows you want to import. When you copy this you'll get a nice and tidy list of records on the clipboard similar to this:

Row_01_Cell_01	Row_01_Cell_02	Row_01_Cell_03
Row_02_Cell_01	Row_01_Cell_02	Row_01_Cell_03
Row_03_Cell_01	Row_01_Cell_02	Row_01_Cell_03

I have the Lines in a Collection version of a For Each action all saddled up!

Here's where I get stopped. I can't find an "iterate through the lines in the clipboard – separate the tab-delimited lines into their necessary components" action even within the entire list of actions.

Of course there is no such thing. You yourself have to build upon the Lines-in-a-Collection action.

Here's an example that should get you started:

EXAMPLE ⇢ Iterate Through Records (Lines of Tab-Separated-Values) on the Clipboard v1.00.kmmacros (15 KB)

I understand/guess that the For Each action will magically 'read' the characters on the system clipboard and recognize the tab characters and thereby perform a further action (the execute the following actions next part) on each now somehow discrete parts of that string.

Nyet.

The For Each action with Lines-in-the-Clipboard does nothing more than iterate through the lines on the clipboard one at a time.

It's up to the user to take that line and do something with it, and I hope I've shown this effectively in the macro above.

-Chris

[BernSh]

Two questions + Bonus Question + two Wish List items:

Is there something to reset or clear between each running of the macro?

Can a named clipboard be further split into three new named clipboards and is regex the only way?

---

Hey Chris,

Good start!

One line at a time works better in this case as the SS rows to transfer must be entered one at a time as a new transaction in Quicken.

To get the copied, parsed by regex, split into variables, variables into their respective clipboards, I found the "Set Variables to Text" action and inserted the Named Clipboard Tokens from the Insert Token drop-down menu. Very Fun!

I then did a "UI Jig" to create a new transaction in Quicken and tabbed and pasted from the now loaded Named Clipboards. Then shift-tabbed my way back to the date field. Also very fun to watch!

It was pretty cool to see it in action, and I thought I was set. However, when I did it a 2nd time, the same instead of newly copied data was pasted into Quicken.

1 of 2 questions:

Is there something to reset or clear between each running of the macro? I’m guessing no as you made the variables 'local variables' which auto-delete when macro terminates per the wiki. If it not a resetting issue then what?

2 of 2 questions:
Can the named clipboard 'Date' be further split into 3 new named clipboards?
I'm looking to separate the date that's in the format xx/xx/xx into 3 two-digit clipboards so I can paste and arrow key my way through the date column in Quicken.

I found these which show doing this via regex. Is that the only way?

How to split up clipboard/variable into multiple variables?

Hello, I'm rather new to KM, I hope you could still help me out with (what might be) a basic problem.. For work reasons, I need to use address information to verify certain things. Most often, the summery will look something like this: Address Slangenburg 13 7608 RS Almelo Nederland Phone 123456789 Website https://www.slangenburg.nl lat. long. 52.376536, 6.691050 I would like to be split up this information into different variables, such as Street name (slangenbu…

Split text from Clipboard into different variables

Hi there! I’ve been trying to solve this for days now but I could’t find a solution. My regex attempts failed miserably… Basically what I want to achieve is this: Go from this input (including line breaks and more than 2 tags) <name>Max</name> <description>Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen…

Bonus Question!
Why set Variable to 0 in part/step 4? Is that so it is cleared for subsequent runnings? Maybe it needs to be done for the local_Field variables?

Wish List:
Option to number actions/steps on exporting macros to Forum to streamline these questionings.

Separate Forum Category for Learning.
Have this category organized by topics and tied into the Wiki.
The Tips & Tutorials ad hoc collection of topics suggest this and doesn’t bring the thinking and organization that an area intended for teaching would have. Having a category specifically for learning would also make space for ‘newbie’ questionings without all the dancing around and labeling one’s intend to learn and utilize.

Iterate Through Records (Lines of Tab-Separated-Values) on the Clipboard v1.00.kmmacros (22 KB)

Sample Apple Numbers SS:
BOA SS to Quicken.numbers.zip (591.0 KB)

[JMichaelTX]

@BernSh

Two questions + Bonus Question + two Wish List items:

Bonus??? What is the "bonus" that we will receive? LOL

@BernSh

Is there something to reset or clear between each running of the macro?

That depends on whether or not your macro needs to reuse the same KM Variables with each execution.
Generally, I use KM Local Variables, which are auto-deledted at the termination of the Macro. So there is no need to clear/reset.

@BernSh

Can a named clipboard be further split into three new named clipboards and is regex the only way?

I haven't studied your macro or workflow, but generally it is better to KM Variable rather than KM Named Clipboards, unless you need to store images, rich text, or objects.

[BernSh]

‘Bonus’ was there for lightness. It worked by getting an LOL. Now to get the macro to work...

@JMichaelTX

...generally it is better to KM Variable rather than KM Named Clipboards, unless you need to store images, rich text, or objects.

This is useful, thank you.

[ccstone]

@BernSh

==One line at a time works better== in this case as the SS rows to transfer must be entered one at a time as a new transaction in Quicken.

Hey Bern,

Not it's doesn't.

You get all the data at once and then let Keyboard Maestro iterate through each line of data (which represents one row in the spreadsheet).

Each time you iterate you process one row of data and enter 1 record into Quicken.

To do otherwise is either more fragile, more labor intensive, or both.

For testing – yes it's best to work with 1 row.

For processing – it's best to work with the entire data set.

To get the copied, parsed by regex, split into variables, variables into their respective clipboards, I found the "Set Variables to Text" action and inserted the Named Clipboard Tokens from the Insert Token drop-down menu. Very Fun!

I'm glad that was fun, but you're not doing yourself any good.

All you're doing is destroying the current values in the variables. You've never done anything to get the data from the variables into the named clipboards.

Named clipboards have their uses, but they should not be used when they add little or no value over a variable (such as the ability to store non-plain-text data, and for long-term storage).

When pasting from named clipboards the data ALWAYS goes through the SYSTEM CLIPBOARD.

In your case I'm guessing the ability to Paste-From-Named-Clipboard seems like a net simplifier rather than a complexifier, but you have to monkey around quite a bit to get the data into the named clipboards from the variables.

It's more efficient to stick with the variables in the first place and simply rename them similarly to what you wanted to name the named clipboards.

NOTE – you can use global variable names instead of local to allow eyeballing data in the variable inspector of KM's preferences. In fact this is usually a good idea when building a macro, because you can also view global variables in the Value Inspector window.

DON'T use Quicken for testing until your paste process is really ready.

Use something benign like BBEdit.

Once you get the paste process working then move to Quicken, but be sure to use a test document and not a working document.

---

Is there something to reset or clear between each running of the macro?

Not in the first macro. In the one I'm about to post I've used global variables, so you can eyeball them more easily – and those are cleaned up at the end of the macro.

It turns out that Numbers wasn't so neatly placing text on the clipboard. You'll see in the first AppleScript what I had to do to make it available to Keyboard Maestro as text.

Do NOT just take this as a template and start building your macro – take the time to understand what I'm doing and why. If you have more questions just ask.

Iterate Through Records (Lines of Tab-Separated-Values) on the Clipboard v2.00.kmmacros (19 KB)

Can the named clipboard 'Date' be further split into 3 new named clipboards?
I'm looking to separate the date that's in the format xx/xx/xx into 3 two-digit clipboards so I can paste and arrow key my way through the date column in Quicken.

Once again – don't use a named clipboard – stick with variables.

And OF COURSE you can split up the text content in any variable or named clipboard to your heart's content.

-Chris

[BernSh]

Working on understanding and put comments inline with actions. Would you confirm my understanding and answer the inline questions?

Found Jim's Regex date splitter How Do I Split Date Parts and Paste Each Part? and incorporated it. Now the DEBUG Found Field Values displays:

07/26/19
07
26
19

Which shows the date splitter worked BUT has a few bugs.

How do I get rid of the 07/26/19 and get the divided date back on one line with an Right Arrow character between each part?

Why did the Payee and Amount values not display?

As Hardy says to Laurel "Well here's another nice mess you've gotten me into!"
Guess that's enough mess for one Saturday!

Keyboard Maestro Actions.kmactions (28 KB)

[ccstone]

@BernSh

Why did the Payee and Amount values not display?

Because you didn't make the variable names in the DEBUG Display Text Window match up with those that were declared.

[BernSh]

Here's the workable macro for anyone who might be interested. The spreadsheet rows must be contiguous and it is only set for data in three columns.

Variables would likely need to be adjusted depending on your data.

It's uploaded disabled and need to be enabled to use.

This is a working and study macro for me and hasn't been polished by removing my embedded comments/questions and other testing steps, extra pauses, etc.

Test carefully using BBEdit or equivalent before using with Quicken!

I'm thrilled to have it work !

Thank you @ccstone and @JMichaelTX!! It would not have happened without your support!

Iterate Through Records (Lines of Tab-Separated-Values) on the Clipboard v2.00 Macro (v9.2)

Iterate Through Records (Lines of Tab-Separated-Values) on the Clipboard v2.00.kmmacros (41 KB)

[JMichaelTX]

@BernSh

Here's the workable macro for anyone who might be interested.
. . .
Keyboard Maestro Actions.kmactions

@BernSh, in case you were not aware, you did NOT upload a macro.
You uploaded a set of individual Actions.
It is almost always best to upload the macro.

Select the macro in the KM Editor Macros pane, and then upload using the Share button/menu. For detailed instructions, see How To upload your macro .

[BernSh]

Thanks Jim.

So selecting the actions and sharing uploads the actions while selecting the macro and sharing uploads the macro. Makes sense as this allows any particular actions to be uploaded versus uploading all the actions that uploading the macro provides. I wondered about why the upload titled itself 'Actions'. It was a hint I stepped over.

If someone downloaded all the actions I originally uploaded (I deleted them and uploaded the macro) they would import a bunch of actions and have to assemble them into a single macro?