Using Macros and Spreadsheets in a Regulated Environment

1. Using Macros and Spreadsheets in a Regulated Environment Ludwig HuberE-mail:ludwig_huber@agilent.com Home Rev 1.00

2. Content • FDA requirements and inspection observations • Problems with spreadsheetprograms and Macros • Policies and procedures for development and use of spreadsheets and Macros • Validation of spreadsheet calculations and Macros • Example using functions built in spreadsheet to increase reliability, integrity, security

3. Part 11 Validation Guidance Draft as of July 2002 http://www.fda.gov/OHRMS/DOCKETS/98fr/001538gd.pdf FDA Requirements • No specific guidelines for M&S • Spreadsheet Calculations and Macro Programs used in GxP environments should be validated • End users should validate any program Macros and other customizations that they prepare • Testing should cover full range • Spreadsheet Calculations and Macro Programs should comply with 21 CFR Part 11 (E-records/signatures)www.labcompliance.com/e-signatures

4. FDA Part 11 Validation Requirements • Setting requirement specifications • Extent of validation should be based on risk assessment. • Verification of ‘proper’ software installation • Functional testing should be quantitative • Functional testing should include stress conditions, boundary testing and high load testing • Updates should be controlled and documented • Regression testing after software changes, e.g. updates

5. FDA Part 11 Testing Requirement Part 11 Validation Guidance (draft) • Test conditions should include not only "normal" or "expected" values, but also stress conditions • Test conditions should extend to boundary values, unexpected data entries, error conditions, reasonableness challenges (e.g., empty fields, and date outliers), branches, data flow, and combinations of inputs. http://www.fda.gov/OHRMS/DOCKETS/98fr/001538gd.pdf

6. Warning Letters • There is no documentation covering Excel application software, or any procedures instituted covering the protection of electronic records or an established back-up system (069) • Failure to validate computer software used as part of the quality system for its intended use according to an established protocol as required by 21 CFR 820.70(i). For example: Software such as Excel, Access, and Word used to create and maintain data bases (rejects, complaints, and concessions) and electronic documents, is not validated (W 073) Timely, specific FDA‘s current thinking? Reference: www.fdawarningletter.com

7. Warning Letter - Full Range Testing • Failure to use fully validated computer spreadsheets to calculate analytical results for in-process and finished product testing [21 CFR 211.165(e)]. For example, the computer spreadsheets used to calculate analytical results for... have not been validated. • QA/QC Spreadsheet Validation, is deficient in that only a small range of values are being used to challenge computerized spreadsheet mathematical calculations. Validate Spreadsheets over wide range Ref: www.fdawarningletter.com

8. Question/Answer: Paul Motise No official FDA policy but important to know Question:Can we still use spreadsheet programs like MS Excel, even though we know that they lack some functionality that are required by part 11, e.g., audit trail? Answer“If you are using Excel for something that is required by a predicate rule, then you generate an electronic record and part 11 applies.” Ref.: www.labcompliance.com/conferences/august99.htm

9. Business Requirements • More importantly, a number of systematic field have reported errors in 24% of the spreadsheets audited (studies since 1997), and these rates may be low because of limits in inspection methods. Overall, the evidence that spreadsheet errors are fairly common is quite strong. Ref: http://panko.cba.hawaii.edu/ssr/devexpt.htm http://panko.cba.hawaii.edu/ssr/cases.htmhttp://www.kamalasen.com/rajalingham-99.pdf http://www.bized.ac.uk/virtual/vla/theories/spreadsheets_background.htm

10. What are Macros? • Mini programs that performs a specific task - to repeat steps perfectly and with much greater speed. In Excel Macros are written in a programming language called Visual Basic for Applications (VBA). • In Excel Macros may be but don't have to be created, they are recorded as keystrokes and mouse actions Add functionality to, e.g., Word, Excel

11. Problems with Spreadsheets and Macros • Easy access to programs • Anybody (not trained on validation and computer science) can write programs • Calculations easy to change • No validation, no documentation • Many different environments (operating systems, PC hardware) • Many versions in use (local PC, server, inbox, delete folders) • Typically do not comply with regulations (e.g., part 11) • QA is not aware of spreadsheet use

12. Develop, implement and enforce procedures for development and use of Macros and Spreadsheets with security, validation and compliance mind. Develop gap analysis, risk assessment, corrective action plan Alternative Solutions to the Problems Part 11 • Use other programse.g., perform calculations in secure and validatedAgilent ChemStation or Cerity environments with functions for part11 Limited part 11 Part 11? • Evaluate add-on software with more security and compliance functionality

13. The eInfotree™ Digital Compliance™ Platform from CIMCON Software, Inc. is the life science industry's first and only available solution that provides 21 CFR Part 11 compliance for Excel spreadsheets, Databases and Files/Documents in a single system 3rd Part Software `Add On` Software for Part 11e.g., for Excel • Wimmer Systems- DACSwww.wimmersystems.com • Stelex - Compliance Builderwww.stelex.com • Cimcon SoftwareeInfotreewww.part11solutions.com Check if they meet your requirements

14. Steps Towards Compliance • Develop, communicate and enforce a company policy and master plan for spreadsheet calculations and Macros • Protect Spreadsheets and Macros using built in standard software and IT infrastructure (e.g., client/server) • Prepare a list with all computers running Macros and Spreadsheets • Standardize ‘development and use’ as much as possible • Validate spreadsheet calculations and Macros • Document your plans and other activities

15. Develop, Communicate and Enforce a Company Policy ‘Standalone‘ document‘ or ‘Add on‘ to Quality or Validation Policy Development and Use of Macros and Spreadsheet calculations used in regulated environment must follow documented procedures. Minimum requirements for such Macros and Spreadsheet calculations are: • All Macros/spreadsheet calculations used in GxP environment should be registered and controlled • Should be validate • Should be protected against against accidental and incidental changes

16. GxP relevant? • __________ • __________ • __________ • __________ • __________ • __________ • __________ • __________ • __________ • __________ • __________ Define Applications in a Regulated Environment • Travel expenses • Time and labor • Commercial business reports • Evaluation of analytical data • Ad hoc evaluations • SOP administration • Reagents administration • Equipment calibration records • Training records • Stability data • Others?

17. Standardize Software, Hardware, Development and Documentation • Use the same vendor for spreadsheet programs, the same product and version for the application software and for the operating system • Limit PC hardware to certain vendors, model, hardware configuration • Standardize IT infrastructure Efficiency, reliabilitysmooth operation • Standardize security procedures • Standardize validation during development and use • Documentation (type, structure, content)example: system inventory, validation report • Naming of data files, documents and attachments

18. Spreadsheet program, version: Operating system: Spreadsheet name: Spreadsheet version: Application: Release date: PC hardware: Description: Vendor, Model: Asset number: Location: Date of installation: Contact for development: People authorized to use: Excel 2000 Windows 2000 Retention time.xls 5.01 Precision of retention times April 5, 2000 Labtop HP Omnibook 4100 237537 Application lab, building 423 May 5, 1999 Mike Win, Telnet 6745234 Curt Myers Standardize System Documentation Use data base with sort functions for application - PC - user - department - validation status

19. Standardize During Development • Code conventions • Name conventions, e.g. mean_value • Code annotations Guidelines for the developers • Formulas for specific tasks • Colors for cells, e.g., use the same colors for entry fields (yellow), results in spec (green), alerts (red) etc. • Spreadsheet annotations (e.g., file name, version) • Validation and testing

20. Use IT Infrastructure and Software Functions to Increase Security & Reliability • Protect computer hardware and access to software (user ID, PW access to computer operating system) • Run Macro/Spreadsheets from secure server • Protect cells, worksheets and VBA scripts • Use versioning for Macros and Spreadsheets and include file and version number in spreadsheet print-out • Include file name and location in spreadsheet print-out • Activate and protect tracking function

21. New Book Includes Chapter on Macros&Spreadsheet Validation Ordering info:www.labcompliance.com/books Validate Macros&Spreadsheets • Validation policy • Validation master plan • Validation project plan • Life cycle approach • Requirement specifications • Design specifications/development • Testing by developer and user • Installation and testing • On-going use and change control • Validation report

22. Develop&Communicate a Validation Policy Policy • All Macros should be validated according to national and international regulations and according global company policy and validation master plan • Only validated Macros should be installed and used • Use life cycle principle for validation • M&S should be under change control procedure Apply common software validation practices

23. With chapter on Macros/spreadsheets Template: www.labcompliance.com/books/masterplan.htm Add Macros&Spreadsheets to the Validation Master Plan • Scope (add Macros and spreadsheet examples) • Responsibilities • Inventory documentation, example • Template for risk assessment • Templates for project validation plan • Change control • Archiving of documentation and data

24. User requirement/Functional Specific. Installation and test in user environ. DesignSpecifications Test in development environment Code developmentCode review Functional testing can be combined for single users Life Cycle Model for Macros Change control Validation plan Validation report Modified V-model

25. Anticipatedrisk situation Preventive action Probability Severity Purchase redundant system Test equipment fails low high Requirement specifications inadequate Rigorous verification with many users high high Risk Assessment • List possible risk scenarios • Estimate probability and severity • Suggest preventive actions

26. User Requirement Specifications Frequent question: Why should I write requirement specifications, everybody knows what the spreadsheet does? • Describe what the problem is, how it is solved today and how the Macro can help to improve • Describe workflow • Collect inputs from other users FDA Part 11 Validation Guidance (draft): Without first establishing end user needs and intended uses, we believe it is virtually impossible to confirm that the system can consistently meet them Most important step of validation

27. Attributes for User Requirements • Clear • Unambiguous • Uniquely numbered • Understandable by the programmer • Testable and • Linked to test cases • Classify importance (optional)-high, medium, low-

28. Number Requirement Test case Software must validate input range (e.g., 0.5 to 30 min) and alert the user in case input is out of range 3.1 T14, T15 Calculate and report relative standard deviation 3.2 T09 Requirement Specifications Table (selected examples) Can use word processors, spreadsheets, data base, or specific application software

29. Requirement Specifications - Good and Bad Examples - • Entry fields should be colored easy to recognize • Operating hints should be part of user interface • Must be self explanatory • Should not need extensive operating manuals • Must be easy to use #3 to 5 difficult to quantify&test Testable and quantifiable

30. Write&Review Design Specifications Defines how functional, security and regulatory requirements are best implemented • Define workbook structure e.g., single or multiple worksheets • Describe formulas and the rational behind them • When using Excel define to use Excel functions or VBA script Developer • Describe structure of code for Macros • Define cell contents for spreadsheets • Describe how to implement security requirements • Define how to handle error messages Review design specification with peers (and with users)

31. Can reduce error rates Design Macro/Spreadsheet for Error Detection • Spreadsheet should detect errors during data entry - wrong type, e.g., string characters instead of numbers - wrong format (e.g., date) - wrong sequence of entries - wrong data range • Spreadsheet should prompt the user in case of wrong entries, and not crash

32. Paul Motise, April 2002, IVT Conference Airlington“The author can attend the formal review meeting but should not bias the objectivity” Objectivity Develop, Review & Annotate Code • Write code using design specification as basis • Document code using standards, e.g., for header - Anybody with similar knowledge should understand it and be able to modify- • Review code with Peers. • ensure objectivity!

33. Testing - General Procedure • White box/structural testing by programmer • Black box/functional testing by user • Develop test cases for the functionsdefine acceptance criteria, test, evaluate, document • Test plans written by developer or QA FDA Part 11 Validation Guidance (draft): Computer system validation should be performed by persons other than those responsible building the system.

34. Testing - General Recommendations • Test cases should be designed for reuse • Test under stress and boundary conditions • Tests should be “quantified” not just “pass/fail” • Tests should be linked to requirement specifications • Apply the concept of risk assessment for the extent of testing FDA Part 11 Validation Guidance (draft): 1) End users should conduct functional testing of software that covers all functions of the program that the end user will use.2) Test conditions should extend to boundary values, unexpected data entries, error conditions, reasonableness challenges (e.g., empty fields, and date outliers), branches, data flow, and combinations of inputs Test plans should be authorized and signed before testing starts

35. Risk Assessment When you determine the appropriate extent of system validation, the factors you should consider include (but are not limited to) the following • The risk that the system poses to product safety, efficacy, and quality; note that product means the FDA regulated article (food, human or veterinary drug, biological product, medical device, or radiological product); • The risk that the system poses to data integrity, authenticity, and confidentiality; and, • The system's complexity; a more complex system might warrant a more comprehensive validation effort. Ref: FDA Part 11 Validation Guide (draft)

36. FDA Part 11 Validation Guidance (draft): • Test conditions: test conditions should include not only "normal" or "expected" values, but also stress conditions (such as a high number of users accessing a network at the same time). • Test conditions should extend to boundary values, unexpected data entries, error conditions, reasonableness challenges (e.g., empty fields, and data outliers), branches, data flow, and combinations of inputs Boundary, Error Condition Testing Define boundaries in requirement specifications • Define test cases at boundaries • Define test cases outside boundaries

37. Part 11 Guidance on Validation and Glossary (draft)We consider regression testing analysis to be an extremely important tool that should be used to assess portions of a system that were themselves unchanged Regression testing: Rerunning test cases which a program has previously executed correctly in order to detect errors. Change Control Frequent changes: bug fixing, new functions • Follow written procedures • Evaluate impact on validation • Initiated by user, authorized by supervisor and QA • Statement on testing • Regression testing for all key functions

38. Date Form ID Location System ID Description of change Include reason for change Evaluate impact on validation Impact of change Authorizationto change Name Signature Date Implemented Date of implementation Comments E.g., document observations, testing Name Signature Date Completed by Name Signature Date Approved by Example: Change Request and Control Form Reference: L.Huber, Validation of Computerized Analytical and Networked Systems, 2002 Interpharm Press

39. Existing Macros and Spreadsheets Frequent question: Why should I validate a spreadsheet application that I have used successfully for many years? Minimum requirements • Describe the current use • Describe the functions the M&S has • Describe any experiences from past use • Document formulas and other key design elements • Document the source code. • Perform functional testing. • Develop test cases for on-going use • Make a statement that the M&S is formally validated. Extent of validation depends on risk assessment Impact on product quality – number of users – frequency of use, complexity, experience

40. A good validation master plan combined with a good validation report ensures high probability to survive inspections Write the Validation Report • Validation approach • Summary of validation steps • Summary of test results • Formal validation statement • To be signed by management representative

41. Problems During Routine Use- Reason for Problems - • New users are working with theSpreadsheets without training • Non authorized people are using it • Users change the Worksheet and save the file under the same name without documentation • Users make errors during data entry Most problems come from wrong operation

42. Use ‘original‘ spreadsheets • Read only access from servers Routine Use- Recommendations - • Train new users • Restrict use to authorized persons • Protect worksheets and cells • Print directory/path/file name with results Limit type and range for data entry Avoid problems

43. Example • Calculates mean and absolute and relative standard deviations of retention times in chromatography • Excel Spreadsheet from a laboratory, can be applied to other applications • Validates defined input values • Report graphical and numerical results • Protected against accidental and incidental changes to the extent possible • Access the worksheet from the Server Keep it simple Show the concepts

44. Three categories of specificationstasks – usability – security/integrity Example - Tasks • Calculate and report mean value • Calculate and report absolute standard deviation • Calculate and report relative standard deviation • Check for and report highest data entry • Check for and report lowest data entry • Display retention times as graphic (value vs. number of entry) and as table

45. Example - Improve Usability • Highlight entry fields with light yellow for easy recognition • Highlight results that are not flagged in blue color • Highlight results that are flagged in red color • Create the output such that all information fits on a single page

46. Example - Avoid Errors During Data entry • Check and alert the user if entry is below lower or upper limit. • Check and alert the user to verify if result is above limit With VBA (not included in the example) • Check and alert the user if a single data entry is 50% above average. • Check and alert the user if a single data entry is 50% below average.

47. Directory, path, file name Spreadsheet Protection&Integrity • Protect all cells not used for data entry • Protect use of Excel sheet by passwords • Display directory, subdirectories, file name and sheet name at the bottom of the Spreadsheet • Display date and time of last data entry • Display&print operator name With VBA (not included in the example) • Disable menu and tool bars to limit function to the intended use • Disable ‘save’ and ‘save as’ • Disable cut/copy and paste control keys

48. Operator Name Data entry Yellow cells Results Green cells Flags Red cells Graphical Results Directory Path&file Operator Hints Date &Time Print on Single page

49. Additional Resource Macro & Spreadsheet Quality Package • Interactive Video Presentation for training • Primer as reference; checklists • Spreadsheet with validation documents (e.g., 21 requirement specs and associated test protocols) • SOPs • FDA Warning letters Ordering info: www.labcompliance.com/books/macros

50. Thank You Ludwig Huber