A) Data base: open source packages
Server: Mysql Server: Postgre SQL SQL Files: SQLLite
Use Graphics to convey and understand the structure:
MySQL Workbench DB Designer
B)Python: Data Manipulation
Files: csv, txt, sql, xml, xls, other text file formats
C) make, shell
R, SAS, SPSS
F) MAPLE (symbolic computing)
G) SED (stream editor)
H) XSLT (Transforming xml to xhtml, txt, html)
2. Employ a simple and efficient architecture:
● Database: store easy access ● Manipulation python code data extraction SQL compilation to produce XML ● XSLT XML to HTML XML to XML ● Web server
3. Use a source control application and work with logged releases.
● CVS ● Subversion ● 0.11.0015 version
4. When you find a problem you fix it, and introduce a test that you will run after any changes done to the code.
● Equal effort to testing, developing; Testing = Developing
5. Always have a working version
6. Work with a end user as he /she uses the system. Know your users and the information the user needs.
Data Process - input data process/output data process. What data is available and used on each step. Accounting Process - input/output data required for popper accountability. Accountability for people and data.
Credit card: Did you use your credit card sir? Maybe my wife used it? Which one? I have 10. Insurance: Why there is 10 very similar accidents in this area in past 10 months? Is that normal/abnormal? Who are the owners, agents, mechanics ?
7. Understand benefits of your approach to the user (compare to others) and show evidence as much as possible :
● Google vs. Yahoo
accurate vs. user experience
● SAS vs IBM
Do what you want with the data vs. use our database
● User acceptance of the software ● Capture things that other system can't ● Thing that will make your software over the “hump” of user acceptance and usage.
8. Don't forget the reporting the user needs.
● Activity level ● Trading, tracking system (you need reports)
9. Track bugs and put testing around any bug you find. When new release come up, test the old bugs on it.
10.Keep it as simple as possible
- Example names:
Lucia Gil-Soto Carlos A Rosario-Rodriguez Raul Hernandez Ramos
Database, database, database! Mysql or Postgres. Database doubles with binary speed each month. Cross Platform!!!. Window, mac, linux !!! You can achieve it via browser (with special cases for each browse) or you can do it via gui library with special cases for each platform. You can use tool like Turbogears2 to build this system. You can use OpenOffice to create documents, pdf, with nightly process.
zip code and territory
- territory key should always be based on zipcode, city name, and effective date. Effective date should be assign per zipcode or per a group of dates depending how often you change the territory.
- There are some situations where city "chicago" might have multiple zipcodes which belong to different territory.
- Table that holds vehicles should always be looked up by the year ,make, and model. You can have a primary key on it but it shouldn't be used to reference car information.You should have a key on year make and model. Car info should be copied over to proper record. This will allow you to wipe out the whole table and import new year cars into it in one process. If somebody changes car or deletes the car the information is not available only on a new lookup. This makes things easy as well as provents any errors from constant updating the vehicle table, as well as it will simplify reporting on policies/claims.
Diary on quote
- The system needs to allow a diary on the quote, so that unacceptable quotes can be marked as "do not convert" with a diary note stating why not to issue this policy.
Insurance Policy as contract
- Treat each insurance as a contract.
- This Helps to distinguished between various parts of policy change and it will create a much standard way to deal with premium changes.
- First contract has an effective and expiration date.
- Endorsement causes previous record expiration date to change. New record is created with effective date of today and expiration date copied from previous record.
- Renewal goes through similar process.
- Cancel changes expiration date.
- Reinstate with no laps cancels the cancelation.
- Reinstate with laps ends previous contract with expiration date x, and creates new contract with effective date of y and expiration date from previous record.
- You run into problems when future cancels and reinstatement need to show up in the accounting records or history(For 99.9% of companies that is not the case, so you just need to keep a history of transaction). These records are not actual really cancellation or reinstate. Since you have to give notice to the insureds that you will be canceling their policy the future cancel and future reinstate handle that process. As these records don't have any bearing on the premium at all unless they become really cancellation they are needed to inform insureds/agents that policy might expire if they do not make a payment. 90% of the time the the future cancel and reinstate will cancel each other out if few days. The remaining 10% will actually become a cancellation.
- The key should be based on agent#,state,line of business.
coverage and limits
- Coverages are divided into following categories:
- SPECIAL MED(ADD)
- There is a group of coverages: Liability, Comprehensive, Other. Each of these groups have its own coverages, coverage limit, coverage deductible. Liability can include (BI,PD)(Indiana)in one state and include (BI,PD,UMBI) in another (Illinois).
Depending on a state the liability group might change.
- All policy records should have this primary key pattern. Primary key, policy number, history number. The two keys policy number and history number should be able to tell you what has happen to your policy.
- If sr22 flag is yes and history number is 1. Send sr22
- If sr22 flag is yes, check the previous records by history number. If previous record has sr22 flag as No. Send sr22
- As underwriting processes cancels of sr22 policies they need a warning (pop up message ) stating "This is an SR22 Policy" or "This policy has a financial responsibility with a state" when we try to cancel such policy.
- If you design a system where you would have to go to driver screen, and look for sr22 flag there you will double the processing speed of that cancel. Unfortunately going through extra step not efficient as it doubles our processing time, so allowing such a warning on such a sensitive policy, reduces the amount of errors by more then 50%.
- Custom colors. (older people need bigger resolution, better contrast in colors)
- View changes after calculation. View written, full, or change in premium that is happening on this transaction.
- Dec pages where you can pick any of the above numbers.
- rewrite options that allows for rewrite with changing policy number, or rewrite with same policy number.(owners to non-owners policy, Lapse in coverage over x-amount of days, lapse in renewal). optional: don't keep policy number if conditions of the contract change.
- automatic vs manual transactions: (cancellation due to non-payment, vs underwriting cancellation. )
- Endorsements should allow for multiple transactions to be done. (change veh,driv,cov,etc... and endorsement reasons should allow for multiple entry)
- Endorsement transactions are (add,change,delete xyz)
- Non-renewals and underwriting cancellations are topics that are controlled by some states and should be customized to allow for reason to be entered. Evaluation of state requirements is needed.
- Web should be a separate entity. It should use some standard open source communication protocol to get business rules and rates.
Browsers and data
Never create browsers that display all data. As soon as number of records reach 50,000 your program will take minutes to display anything. If you want to show some records make sure you use Limit 100 and SQL DATABASE is REQUIRED. No Flat file based db should ever be used. Start with true database from day 0. Mysql Postgres etc.
- The UI should have "save" or "close" buttons and not "ok" and "cancel"
- The username should be copied to a updateduserid as "firstnamelastname" or "flastname" no sid references as it people might leave and new person will be assign same id number.
- Everything should fit on 800x600 screen. Older people even do they have monitors with 1200x1080 can't see letters that well and they WILL change the resolution to 800x600.
You will default state. When underwriting people work they will start divide the work by state. You should have a default state ex. IL in which you filter out any other states data. It speeds up the data retrieval and makes things easier for end user.
Process at Night
- In order for system to work properly you will have to process at night. You allow users to print individual page (when they look at it; on screen, or you provide them with a link to rtf,pdf,or odf file after its been processed ). The nightly process will cover all transactions (cancel, reinstate, endorsements, new business, etc). Here is a sample process that needs to run. It shows you the level of complication it requires and the only way to handle them is to make batch scripts that handles each job separately, and can be maintain separately.
- Get Agency Bill List
- Get Daily Transactions
- Delete Daily Work/Report Files
- Process Quote, then delete Quote (add follow up to date to users ical calendar(mozilla calendar)
- Start Processing Policies
- Fix,check records (transactions order,integrity of records)
- Process Cancellation in Accounting, Policy, Policy History, Renewals, Offers
- Process any Missed records.
- Process Renewals in Renewals, Offers,Policy, Policy History, Accounting (Create report for "not to be renew")
- Delete old transactions, and create a new list of transactions to process.
- Process Transactions,Then Process Transactions again. (This is needed if there were 2or more transactions that need other files to update before they can be applied)con
- Move Transactions to Temporary Table
- Read Temporary Transaction table and create files, then add to be printed table.
- Add Transactions to Monthly group (Transaction that will need to be processed ex. 6 months renewal)
- Update Monthly, weekly transaction statistics table.
- Add Transaction Error to the Log or Error Table.
- Policy and claim number should be customized based of few fields. state, line of business, year of the policy, year of the report, (year should be customized XX vs XXXX) prefixes, auto number starting from a range, ending at a range and going to a next range.
- Claim needs to be combined with a policy image, and check policy eff,exp dates at all times. The claims and policy needs to be seperated but integrated at the same time to allow underwriting to view claim details, and claim to view underwriting history.
- Each action on money needs date, time, uid. open reservers, request check, approve check, issue check, print check, reprint check (manager only). User needs to be able to reprint checks, and you have to have a records of it. You also will need to provide easy way to get list of checks processed today in some csv file that can be imported to bank to let them know which checks are valid. This prevents a lot of fraud that insurance company deals with. If you can only cash the checks that is on a list that insurance company sent the bank, then you just got yourself best protection against fraud.
- Each check needs to be broken down into parts. subcheck1 =40, subcheck2=50,subcheck6=40 (select subchecks) and create final check. Update the subcheks with the final check#. If voided mark subchecks as voided. Ask if payto name is the same on all subchecks. If yes let user put in multiple subchecks. If different he/she needs to process each subcheck, then check separately. Alternative to this is one check which allows for adding "pay on reserve" where you would add the reserves code and the amount you want to pay. Enter the reserve, enter the amount to pay (ask user if he wants to close the remaining amount). On save, create a (check#,reserve# unique key)and one extra record check# reserve empty which will be the final check)
- Monthly and year end reports will need to have an archive copy of report and data. So if you have a open reserves report done on 12-31 make sure you have a pdf copy of the report and csv file of the data that this report will consists off. The reason is that state, reinsureds, or other insurance medium might request the data 6 months after its all said and done for the review. Having such data available is to make sure things go smooth.
- Each claim needs to have a policy state, to be able to find out which state is this claim responsible for.
- From claim records you need to be able to identify the insureds name as stated on the policy and name of insureds that was driving the car at the time of the accident.
- Cause of Loss could be added.
- Tagging should be supported to allow for tags like "total loss", fire, vandalism, etc.
- Claim record should have total amount paid so far.Each claimant should have total amount paid for that claimant.
- Diary key should be build on claim no and diary sid and optional claimantnumber which will allow to write diaries on claimants
- The tagging should be implemented to allow for tags like: "fire","theft","Total loss", "recovery". Similar to a blog tagging.
- Start with cancellations with a proof of mailing, then new business, endorsements, reinstates, rewrite, renewals. There should be a capability to pick which printer each of these is going to and and from what tray each jobs come from. For example the deceleration page can be on the printed on a normal paper while id card is printed from a different tray with a different paper.
- Finance company is a special agent/broker that can send a cancellation to an insured on their own (legally). Regular agents have to do it through insurance company. Difference is that if there is a cancellation via regular agent (you have to add X amount of days notice to cancellation ) cancellation date = Today + X days. If the cancellation is coming from a finance company then you cancel it as stated on a cancellation sheet since finance company already sent insureds a notice.
1. Dispatched: The initial state for a new CR. 2. Incomplete: Something critical is missing from the CR. 3. Accepted: CR was accepted, the first step in being investigated and fixed. 4. Defer: Work on this CR is on hold. 5. Cause Known: A basic understanding of the problem is known. 6. Fix Understood: A basic fix is now understood. 7. Fix in Progress: The assigned engineer is actively working on the fix or getting it integrated. 8. Fix Available: A changeset or the actual fix has been made available in a team area. 9. Fix Failed: Something went wrong with the fix. 10. Fix Delivered: The changeset or fix has been integrated into the master area and will show up in the next build promotion. 11. Closed: There are many reasons why a bug will be in the closed state. It might be verified as fixed, closed as a duplicate, closed as 'not a bug' or closed as 'will not fix'.