Manipulating the Excel (XLS) files created by Bank Genie

[ideasguy]

Heres a solution which will be of great interest to those who like to massage the query/report data exported from the Transaction Analyser program in Bank Genie.

First the enquiry by Ray:'

I can’t remember whether I have mentioned this before but when I save a report in excel there are two versions created (not sure why two) but neither version allows me to use formatting commands that I normally use on my spreadsheets.

e.g. In all my financial reporting I like to have two decimal places and commas to separate the thousands and there is a ‘comma’ command that does this on any data that has been highlighted. When I try to use this on either version of the BG spread sheets I get the message Style ‘Comma’ not found.

The numerical data in the BG spread sheets has either one or two decimal places depending on whether the number has a final zero or not e.g. 12.50 appears as 12.5 and there are no commas to separate the thousands.

My first reply:

The (Bank Genie Transaction Analyser) program creates two types of Excel file.
As you have found out, Excel is not exactly backward compatible.
At the time of doing the program, I didn’t know which of two versions to use – the current version (at that time) or an older version.
I decided to make it create both.
That explains why there are two!

Ive checked, and I have Microsoft Excel 2007.
That does seem quite old!
What version are you using?

You appear to be using a more recent version than either of the ones the program is creating.

Lets try this.
Open your current Excel program.
Open one of the xls files Bank Genie has created.
Choose the Save option.
Is there a method when saving the file to convert it to the new version?
If it does, does it then allow you to do all the normal things you can do with Excel program?
I’m hoping so, and eagerly await the result of this experiment!

If successful, does it convert both the BG types?

and the reply from Ray:

I am using Office 2010 and I have now tried saving as Excel Workbook which puts an extension of .xlsx and that works on both your files and allows me to use my formatting functions.

Has anyone else figured this out?
I'd like to hear from other Bank Genie members who can give this solution a try.

[barryl]

I have not wanted to play those tricks but I normally convert to ODS (Open Office format) to manipulate spreadsheets.

[ideasguy]

Ooops!
Sorry for misleading.

The board Tips & Tricks (where this posting was made) are things you can do with Bank Genie.
The "massaging" of data after exporting (nothing to do with Bank Genie) refers to (here) how some Excel experts like to reorganise the presentation - e.g. Columns, and the things described by Ray, etc.

Thanks for posting yet another method Barry.

[Phil]

  I use Office 365 - have to reset privacy to "allow editing" and then convert to *.xlsx but from there all works perfectly - just want to say that this facility is a very useful tool indeed - particularly at "Income Tax Return" time of year - thanks again for a great piece of software which "came to my rescue" when Microsoft Money "disappeared"

[ideasguy]

Nice to hear from you Phil, and thank you for your encouraging comments

Every time I do my Tax Return, I spend as much time making tweaks to the Bank Genie software as I do on the actual boring Tax stuff!
I'm afraid I always leave it until January each year.
This year was no exception. I'm using and doing the latest enhancements to the latest version under development.
Good to see so many Excel user folk have found that the XLS files as generated by Bank Genie are (apparently) quite easily adapted for use in higher versions of Excel and Open Source editors.

[barryl]

Just by the way it may well not be worth maintaining the generation of the older Excel format, and it would be less confusing to users to see only one resultant .xls file.

[ideasguy]

Yes, I agree. I'll eliminate one of the output files Barry.

[pencaemawr]

Excel versions are generally backward compatible - you can read old files on new versions. Installing the Microsoft Office compatibility pack enables older versions of Office to read the newer versions of Excel, Word and Powerpoint, i.e. read xlsx docx pptx files.
https://www.microsoft.com/en-gb/download/details.aspx?id=3
Sorry if this is a long time after the original thread, but I was catching up and thought it might be useful to someone.

[ideasguy]

Thank you for posting this John and welcome back to the forum!
The export to XLS feature applies to Bank Genie and also Ideas Genie. I've been asked to allow plants list reports to have that XLS option available.