If you live long enough and move around, you can make connections in the oddest ways. This past week I had the pleasure of learning about a former colleague's grandmother.
I recently announced in the Press-Telegram Alumni Group on LinkedIn that I started a new position with the Pascua Yaqui Tribe. My former colleague Brian Johnson was kind enough to offer his congratulations and volunteered that his grandparents had taught English to the Yaqui people several decades ago. I asked for their names and he told me his grandmother's name was Lucille Hand.
This past week I was chatting with Lina, who staffs our reception desk. The topic of languages came up, so I took the opportunity to delve deeper. She grew up speaking Spanish and Yaqui, not learning English until 6th grade. Mrs. Hand was her 6th grade teacher in Old Pascua! Lina shared fond memories of Mrs. Hand, telling me that she was the first teacher who took the time and care to make sure that her students really learned their subjects. "Good enough" was not good enough for Mrs. Hand.
As it happens, two of the major influences in my life were also teachers. One was Mrs. Tait, my American History teacher, who was a legend at Miami Edison Senior High and about whom I have written in a Facebook high school alumni group. The other was my grandmother Jo Randall, who wound up her teaching career at Westview Junior High in a poor area of Miami. She and I spent a lot of time together and several times when we were out and about, she was approached by someone who had been her student. They all were very thankful for the impact she had on their lives and were eager to share their achievements, hopes and dreams. I am very proud of her work and dedication to learning.
Brian and I had a work friendship for a number of years and I am glad we are still in touch, if for no other reason than having a second degree connection to his grandmother. Judging by Lina's remembrances, she was a special woman. Brian should be very proud of Mrs. Lucille Hand.
Friday, October 11, 2013
Saturday, June 1, 2013
F9 and MAS90 Refresh Speeds
Today I discovered an old post on LinkedIn (https://www.linkedin.com/groups/F9-MAS90-refresh-speeds-51826.S.100899816?qid=217fadf9-d130-462a-a2f3-b447948f74bd&trk=group_search_item_list-0-b-cmr&goback=.gmr_51826) that I sent in response to a fellow group member's question about F9 and MAS90 refresh speeds. It's a highly technical discussion, so definitely not of general interest, but it's content that I want to preserve. It will be much easier for me to find it here than the haystack of LinkedIn groups. :)
F9 with MAS90 is definitely resource-intensive. There are ways to counteract the extreme slowness but no "magic bullet" to make it quick. Here are best practices that I discovered.
MAS90 uses a flat-file database, so searching it will always be slow. First you want to minimize the number of G/L accounts. Try not to add new accounts; instead, merge or renumber as appropriate and purge (or inactivate) unused accounts. Second, keep as few years of transaction history in your "live" database as you need for operational/audit reasons. Third, get into the administrative tools (after backing up, of course) and run the routines that clean up or rebuild the database. If you have the knowledge, get into the control file and hack out "ghost" users. (This is an old problem, so maybe they have solved it by now.)
Your server is another potential bottleneck. If you can, run a 64-bit OS and load it up with RAM, which is the cheapest investment you can make for more speed. If you can't afford a dedicated accounting server, at least make sure that it is running no other processor- or memory-intensive tasks.
Next comes data transfer. You need a fat pipe from the server to your desktop; i.e., gigabit ethernet. Wireless or WAN/VPN performance will be painful. Physically sit as close as possible to your data so that your desktop is not behind any routers or switches. IT pros may tell you it makes no difference but you need even the slightest edge you can get.
On your desktop, also run a 64-bit OS and load it up with RAM. Unload any RAM hog applications (browsers can be culprits) when you are about to crank up F9. Eliminate items from your startup tray, keeping only what is absolutely necessary for security.
I'm sure you have already discovered to set Excel for manual recalculation. Make Shift-F9 single sheet recalculation your best friend until it is time to validate the entire workbook. Also set Excel to recalculate before save and save whenever you are interrupted.
I believe in modular programming for efficiency and debugging. It won't necessarily help recalculation speed but it will help your programming performance. Use BSPEC statements and have them refer to cells, not have values embedded. The same applies to your GL and NGL statements. Build the inputs outside the print range with visibility for editing and debugging.
In general, use as few F9 statements as possible. For instance, build variances, sub-totals and totals the old-fashioned way. Only use F9 statements to extract values, not to do calculations.
Avoid the wildcard (*). It is powerful but hungry. Instead use a range, such as 000..999. Trim the range for efficiency while ensuring that you capture all data.
Precision programming creates a risk of missing accounts that have been created in error outside the defined chart of accounts ranges. You must build in proof totals with inclusive ranges and accept the performance hit, as financial statement accuracy is paramount. It will help if you place all your proof totals (PT) in one sheet and recalculate it with Shift-F9 as seldom as possible. Create a full set of check totals on that sheet that refers to the grand totals (GT) on each sheet. It should have =GT-PT going across and down. That way when you recalculate you can quickly see whether it has all zeroes.
If you are in a time crunch, ideally you will save, which will force a full recalculation, just once before you publish. Plan that event for a coffee break, phone call, etc. and you will reduce your stress. Also save if you are interrupted so your PC can crank away while you are attending to something else. The more robust or complex the worksheet, the more important this is. Even if you save without meaning to, walk away. Take a deep breath and let the PC do its thing.
You can actually use F9's resource hoggishness to make yourself more accessible. Again, you can mitigate but not eliminate the demand it places on your server and PC. You can ignore my earlier advice, discipline yourself to save frequently, get out of your chair and interact with your staff. Coming in early or waiting until after hours does speed it up a bit but doesn't do much for your work-life balance. Always remember to get your head out of your spreadsheets and be a well-rounded CFO for your team and the people you serve.
F9 with MAS90 is definitely resource-intensive. There are ways to counteract the extreme slowness but no "magic bullet" to make it quick. Here are best practices that I discovered.
MAS90 uses a flat-file database, so searching it will always be slow. First you want to minimize the number of G/L accounts. Try not to add new accounts; instead, merge or renumber as appropriate and purge (or inactivate) unused accounts. Second, keep as few years of transaction history in your "live" database as you need for operational/audit reasons. Third, get into the administrative tools (after backing up, of course) and run the routines that clean up or rebuild the database. If you have the knowledge, get into the control file and hack out "ghost" users. (This is an old problem, so maybe they have solved it by now.)
Your server is another potential bottleneck. If you can, run a 64-bit OS and load it up with RAM, which is the cheapest investment you can make for more speed. If you can't afford a dedicated accounting server, at least make sure that it is running no other processor- or memory-intensive tasks.
Next comes data transfer. You need a fat pipe from the server to your desktop; i.e., gigabit ethernet. Wireless or WAN/VPN performance will be painful. Physically sit as close as possible to your data so that your desktop is not behind any routers or switches. IT pros may tell you it makes no difference but you need even the slightest edge you can get.
On your desktop, also run a 64-bit OS and load it up with RAM. Unload any RAM hog applications (browsers can be culprits) when you are about to crank up F9. Eliminate items from your startup tray, keeping only what is absolutely necessary for security.
I'm sure you have already discovered to set Excel for manual recalculation. Make Shift-F9 single sheet recalculation your best friend until it is time to validate the entire workbook. Also set Excel to recalculate before save and save whenever you are interrupted.
I believe in modular programming for efficiency and debugging. It won't necessarily help recalculation speed but it will help your programming performance. Use BSPEC statements and have them refer to cells, not have values embedded. The same applies to your GL and NGL statements. Build the inputs outside the print range with visibility for editing and debugging.
In general, use as few F9 statements as possible. For instance, build variances, sub-totals and totals the old-fashioned way. Only use F9 statements to extract values, not to do calculations.
Avoid the wildcard (*). It is powerful but hungry. Instead use a range, such as 000..999. Trim the range for efficiency while ensuring that you capture all data.
Precision programming creates a risk of missing accounts that have been created in error outside the defined chart of accounts ranges. You must build in proof totals with inclusive ranges and accept the performance hit, as financial statement accuracy is paramount. It will help if you place all your proof totals (PT) in one sheet and recalculate it with Shift-F9 as seldom as possible. Create a full set of check totals on that sheet that refers to the grand totals (GT) on each sheet. It should have =GT-PT going across and down. That way when you recalculate you can quickly see whether it has all zeroes.
If you are in a time crunch, ideally you will save, which will force a full recalculation, just once before you publish. Plan that event for a coffee break, phone call, etc. and you will reduce your stress. Also save if you are interrupted so your PC can crank away while you are attending to something else. The more robust or complex the worksheet, the more important this is. Even if you save without meaning to, walk away. Take a deep breath and let the PC do its thing.
You can actually use F9's resource hoggishness to make yourself more accessible. Again, you can mitigate but not eliminate the demand it places on your server and PC. You can ignore my earlier advice, discipline yourself to save frequently, get out of your chair and interact with your staff. Coming in early or waiting until after hours does speed it up a bit but doesn't do much for your work-life balance. Always remember to get your head out of your spreadsheets and be a well-rounded CFO for your team and the people you serve.
Subscribe to:
Posts (Atom)