Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

2016 ACTUARIAL SALARY SURVEYS
Contact DW Simpson for a Personalized Salary Survey

Reply
 
Thread Tools Display Modes
  #31  
Old 02-17-2008, 12:20 AM
Expunge Expunge is offline
Member
 
Join Date: Jul 2003
Posts: 9,181
Default

One way is not to follow the oversight in the demo here. at 3:30 he inserts a column, but doesn't fix the grand total or average columns.

http://office.microsoft.com/home/vid...HA102376171033
__________________
John 3:16
Reply With Quote
  #32  
Old 02-25-2008, 11:20 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 79,406
Blog Entries: 6
Default

PDF: Goal-directed debugging of spreadsheets
http://web.engr.oregonstate.edu/~erw...ug_VLHCC05.pdf

Quote:
ABSTRACT:
We present a semi-automatic debugger for spreadsheet
systems that is specifically targeted at end-user programmers.
Users can report expected values for cells that yield
incorrect results. The system then generates change suggestions
that could correct the error. Users can interactively
explore, apply, refine, or reject these change suggestions.
The computation of change suggestions is based on a formal
inference system that propagates expected values backwards
across formulas. The system is fully integrated into
Microsoft Excel and can be used to automatically detect and
correct various kinds of errors in spreadsheets. Test results
show that the system works accurately and reliably.
Easier to read website: GoalDebug

Alas, I see no links on how to get a hold of the software itself. May be they're developing it for commercial use, and don't have a full release yet. Or perhaps they see it just as an academic project.
__________________
It's STUMP

LinkedIn Profile

Last edited by campbell; 02-25-2008 at 12:02 PM..
Reply With Quote
  #33  
Old 06-25-2008, 10:22 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 79,406
Blog Entries: 6
Default

So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #34  
Old 06-25-2008, 11:05 AM
E's Avatar
E E is offline
Eddie Smith
SOA AAA
 
Join Date: May 2003
College: UGA
Posts: 8,813
Default

Quote:
Originally Posted by campbell View Post
So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?
I think one huge problem is poor or no documentation for complex spreadsheets. This would include problems like VBA code that lacks comments or just really large, complex spreadsheets taht lack an "Instructions" tab or accompanying document that outlines their purpose and rules for updating, maintaining, and using.

Poor documentation results in the following problems/risks:

1) Significant lost productivity for future users (technical or not) as they have to spend hours to days to months trying to understand the spreadsheet.

2) Misuse due to lack of understanding of the spreadsheet.

3) Lack of use of the spreadsheet or using spreadsheet to only part of its potential.

4) Having to rebuild the spreadsheet by a future user (call it "re-invent the wheel risk").

I think this risk is particularly high in environments with high turnover. I think advanced spreadsheet users tend to design spreadsheets in a "for my eyes only" way. I'm guilty of this myself. The key is realizing that your product is near useless in the future if you don't document it or don't take the time to "teach" it to someone else.
__________________


Learn how FSA exams are different from the prelims

Registration is open for all of our spring 2018 FSA exam online seminars:

ILA-LP | ILA-LFV-U | ILA-LFV-C | ILA-LRM | QFI Core | QFI Adv | QFI IRM | G&H Core | G&H Adv | G&H Specialty

Check out our Technical Skills Course: Excel, VBA, Access, brand new R material, and more!

Follow us on Twitter, Facebook, and LinkedIn
Reply With Quote
  #35  
Old 06-25-2008, 11:31 AM
GadgetGeek's Avatar
GadgetGeek GadgetGeek is offline
Note Contributor
SOA AAA
 
Join Date: Sep 2001
Location: I'm here, where are you?
Studying for a way to escape
College: Illinois College ('87)
Favorite beer: Stag
Posts: 11,154
Default

Quote:
Originally Posted by campbell View Post
So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?
I have a lot of experience with dealing with the floating point/loss of precision issue. One of my first tasks at my previous employer was re-writing our illustration system (actuarial version that was used to validate ITs version). FORTRAN was the language (ITs was in COBOL). The big "issue" was we needed to be able to match exactly with what would be printed on the policy page as sometimes our runs were used for client service work (often times with legal involved). So I had to use integer math for everything and keep track of how many decimal points were in each of my numbers. Not fun. I resorted to creating an iMult and iDiv function to call to keep things sane from a readability/debugging perspective. When we did the annual dividend updates and printed new rate pages for the agents (pre laptop days ), these runs took a very long time. IT was not happy with me, until we got something worked out to run these at more opportune times.
__________________
Some people are like slinkies. Mostly useless, but provide amusement when pushed down the stairs.
Reply With Quote
  #36  
Old 06-25-2008, 02:51 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,408
Default

Quote:
Originally Posted by campbell View Post
So, after I've written a few articles on general principles of preventing/detecting spreadsheet error, I'm looking to see what direction I should go in.

I've got an idea for a technical article, talking about loss of precision in using floating-point arithmetic, and some tricks to consider to reduce that sort of thing (numerical analysis sorts of ideas.... I know quite a few actuaries have been exposed to this, but many more have not.)

Another idea was dealing with end-user risks, considerations, resources, etc.

Any ideas from people? What kinds of computing/programming/spreadsheet errors/issues would you like to read about?
My vote is to cover the end-user issues. Although I personally would also like to read the technical one.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.


My latest favorite quotes, updated Oct 13, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
Originally Posted by Dr T Non-Fan View Post
Yup, it is always someone else's fault.
MORE:
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.
Reply With Quote
  #37  
Old 06-25-2008, 03:08 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 79,406
Blog Entries: 6
Default

I'm on the EUSPRIG mailing list (it's in Yahoo groups I think.... here it is - obviously, it's not just Europeans on the group), and end user concerns was something that came up recently, in response to this article: http://www.cfo.com/article.cfm/11525407?f=insidecfo

Sloppy Spreadsheets: Readers Speak Out
Readers make some pointed additions to CFO.com's "worst practices" list.

Quick summary:
- formatting sucks, not set up for printing
- missing info: no context/title on each individual sheet, no numbering of sheets for printout (important if you'll be distributing hard copies around -- you can't refer to tab "Sheet1")
- things that look bad when reformatted because of text bleeding over into adjoining cells
- no summary of all tabs for a large file - I have this problem with some of the work I've come across.... a bunch of tabs with similar names, and no index/table of contents
- hard-coded numbers (I've mentioned this in a couple of articles)
- hiding columns/rows with sensitive information in it... and leaving that in when file is distributed to external people. The worst I've seen here is someone changed the format to white on white.... easy enough to change it to be readable. They recommend "protecting" a cell, but that just protects against people inadvertently coming across sensitive info. It is relatively easy to "break" Excel passwords, and I've done it in the past with legacy spreadsheets where the originator was no longer around.
- using a spreadsheet where a calculator will do (I use Excel as an informal calculator all the time. I don't see the problem here, unless you suck at Excel. But then if you suck at Excel with calculation, you'd probably suck with a calculator.)
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #38  
Old 06-25-2008, 03:36 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

Using the Excel row numbers as the primary key for a table (one of my department's main spreadsheets has this quality) - the table doesn't even have column headings, so I pity the fool (i.e. me) who decided to insert a row at the top to type in a description for each column, and inadvertently destroyed the spreadsheet.

Is it so difficult to put in a hard-coded column of numbers that go 1, 2, 3, 4, ... so you're independent of the position on the page?
Reply With Quote
  #39  
Old 06-25-2008, 03:39 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 79,406
Blog Entries: 6
Default

Quote:
Originally Posted by Phil View Post
Using the Excel row numbers as the primary key for a table (one of my department's main spreadsheets has this quality) - the table doesn't even have column headings, so I pity the fool (i.e. me) who decided to insert a row at the top to type in a description for each column, and inadvertently destroyed the spreadsheet.

Is it so difficult to put in a hard-coded column of numbers that go 1, 2, 3, 4, ... so you're independent of the position on the page?
I actually use ROW() for this purpose.... but then I subtract off ROW(NamedColumnHeader) so that people can add in rows at the top if need be. (I like to start my row count with 0.... see here.)
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #40  
Old 06-25-2008, 07:06 PM
Former Actuarial's Avatar
Former Actuarial Former Actuarial is offline
Member
 
Join Date: Oct 2007
Posts: 2,891
Blog Entries: 5
Default

Quote:
Originally Posted by campbell View Post
I actually use ROW() for this purpose.... but then I subtract off ROW(NamedColumnHeader) so that people can add in rows at the top if need be. (I like to start my row count with 0.... see here.)
and likewise, using dget instead of vlookup.
__________________
When I meet you in person, shots on me! Do you bite?

We are a bunch of high roller players who like to play high roller games - Hardinda
Reply With Quote
Reply

Tags
machine learning, predictive analytics, speadsheet error, spreadsheets

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 08:17 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.34804 seconds with 10 queries