New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Programming For Actuaries All Notes

by: Taylor Patterson

Programming For Actuaries All Notes MATH 3550

Marketplace > University of Connecticut > Mathematics (M) > MATH 3550 > Programming For Actuaries All Notes
Taylor Patterson

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

These notes cover all material in class MATH 3550.
Programming for Actuaries
Jim Head
Math, programming, Excel, Access, VBA, Microsoft
75 ?




Popular in Programming for Actuaries

Popular in Mathematics (M)

This 35 page Bundle was uploaded by Taylor Patterson on Thursday March 3, 2016. The Bundle belongs to MATH 3550 at University of Connecticut taught by Jim Head in Fall 2015. Since its upload, it has received 33 views. For similar materials see Programming for Actuaries in Mathematics (M) at University of Connecticut.

Similar to MATH 3550 at UCONN

Popular in Mathematics (M)


Reviews for Programming For Actuaries All Notes


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 03/03/16
MATH 3550 –Programming for Actuaries Notes Formatting Notes -> September 3 , 2015 rd  Formatting o CTRL 1  Format cells shortcut o Make sure that you pick the appropriate number of digits and formats, don’t use too many numbers  Currency:  Does it make sense to include data to the penny?  000’s or M’s  Factors/Indices:  Use number format with the appropriate number of decimals  Percentages:  Use percent format, includes the % symbol  Text:  Many setting for formatting text  Numbers will sometimes default to text when copied fro another sources, use text-to-columns to convert ranges o Be mindful of using color, bold, and CAPS o Centering headings across table exhibits  Merge and center  Center across selection o Print settings & Print range  Check print preview before sending sheets  Set range in page layout  Set to repeat columns/rows in page layout  Charts should be at least one-page wide  Page Setup Menu functions o Set orientation o Set scaling o Set margins o Set header/footer  Specific time, page number, dates, etc. o Set to repeat rows columns o Set print area o Set page order o Include/exclude gridlines and headings  Shortcuts o o CTRL C  Copy o CTRL V  Paste o SHIFT SPACE  Select current row o CTRL SPACE  Select current column o CTRL SHIFT +  Add a cell/row/column o CTRL –  Delete a cell/row/column o CTRL PAGEDOWN  Select previous tab o CTRL PAGEUP  Select next tab o CTRL  Select multiple objects o ALT ENTER  Create line breaks o CTRL SHIFT F3  Auto-name a table th Functions Notes Pt. 1–> September 8 , 2015  Tip: When entering in formulas, it’s a good idea to enter them in lowercase. If a formula is recognized in the formula library it is auto- converted to all uppercase, so you know when you type in your formula incorrectly.  Volatile function: recalculates every time the worksheet calculates  OFFSET o Inputs: (italicizes means that it isn’t required  Reference point  Number of rows to shift  Number of columns to shift  Height  Width o Used to get information from tables  Advanced OFFSET o Data – Date Validation  To create drop down lists  COUNTA o Input:  Range of cells o Returns number of non-blank cells  COUNT o Input:  Range of cells o Returns the number of cells with numerical entries, including 0s  RAND o Inputs: None o Returns a random number between 0 and 1 o Volatile Function  RANDBETWEEN o Inputs:  Value 1  Value 2 o Returns a random value between value 1 and value 2  IF o IF(Statement,”ReturnIfTrue”,”ReturnIfFalse”) o Allows you to perform a test against a range value and returns one value if true and another if false  VLOOKUP o Input:  Lookup value  Table array  Column Index Number  Range Lookup o Looks for a value in a list of values located in the left-most column of a table o Can specify:  Do you want to search for the closest value on the list or find an exact match?  Once the value is found, which column do you want to return the result from? o Use True(1)/false(0) as range lookup if want an approximate match or an exact match  HLOOKUP o Same as VLOOKUP but with row index number instead  ISERROR o Returns true if #N/A o Otherwise FALSE  IFERROR o Allows you to combine IF and ISERROR  DATE o Input:  Year  Month  Date o Returns in date format  DAY o Returns day from a number  MONTH o Returns month from a number  YEAR o Returns year from a number  NETWORKDAYS o Returns number of work days between two dates  NOW o Returns today’s date and time o Volatile  TODAY o Returns today’s date o Volatile  WEEKDAY o Returns the day of a week for a given day  WEEKNUM o Returns the number of a week for a specific year  WORKDAY o Returns the date before or after a specified number of workdays  YEARFRAC o Returns the fraction of the year between two dates  DATEDIF o Returns the difference between 2 dates th Functions Pt. 2-> September 17 , 2015  MATCH o Identifies the placement of a value on a list o Inputs:  Lookup value  Array to check (can be named array)  0(exact value) or 1(approx. value)  MATCH/INDEX o Can be used to mimic a VLOOKUP  Problem with VLOOKUP is that the lookup value needs to be in the left-most column of the table o =INDEX(Table, MATCH(LookupNum, column, 0 or 1),ColumnNum)  COUNTIF o Counting based on one condition o Inputs:  Range  Criteria: <”456”  SUMIF o Summing a range based on one condition o Inputs:  Range  Criteria: “<0”  Sum Range  COUNTIFS o Counting based on multiple criteria o Inputs:  Range  Criteria  Range  Criteria  Etc.  SUMIFS o Summing based on multiple criteria  Sum range  Criteria range  Criteria  Criteria range  Criteria  Etc.  SUMPRODUCT o Multiply same sized ranges together then sums the results o Can be used to do the same as SUMIFS o * converts all values that aren’t numbers into numbers o =SUMPRODUCT((range=”x”)*(range2>”y”)*(range3))  ARRAY o CTRL SHIFT ENTER o Create conditional formulas  Data Evaluation Features: o Pivot Tables  Quickly summarizes data  How to use:  Identify how you want to summarize your date. It may help to identify and draw table headings.  Under the insert tab, select “PivotTable”  Follow the prompts and set up a pivot table  Values shown can include:  Sums  Averages  Counts  Maximums  Minimums  Etc. o Conditional Formatting  Will highlight/format cells based on specific conditions  How to use Conditional Formatting:  Conditional formatting is located in the home tab  Identify the criteria you want to apply  Follow the prompts to set up a new rule Functions Pt. 3-> September 17 , 2015th  Financial Functions: o **Rate and period are in months, in order to get in years, divide rate by 12 and multiply period by 12 o PV  Present Value of annuity  Inputs:  Rate  Nper – Period  Pmt - payment  FV – future value  Type – 0 is annuity due, 1 is annuity immediate o FV  Future value of an annuity  Inputs:  Rate  Nper – period  Pmt – payment  PV – present value  Type – 0 is annuity due, 1 is annuity immediate o PMT  Payment of an annuity  Inputs:  Rate  Nper – Period  PV – present value  FV – future value  Type – 0 is annuity due, 1 is annuity immediate o Rate  Interest rate for an annuity  Inputs:  Nper - period  Pmt - payment  Pv – present value  Fv – future value  Type – 0 is annuity due, 1 is annuity immediate  Guess o NPER  Returns the period in months  To get the period in years divide by 12  Inputs:  Rate  Pmt - payment  PV – present value  FV – future value  Type – 0 is annuity due, 1 is annuity immediate o IRR  Internal rate of return  A metric used in capital budgeting measuring the profitability of potential investments  It is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero  Inputs:  Values  Guess o NPV  The difference between the present value of cash inflows and the present value of cash outflows  Used in capital budgeting to analyze the profitability of a projected investment or project  Inputs:  Rate  Values o Using nested PV’s to calculate deferred payment patterns  First take the present values of the payments  Then take present value with  Rate = rate  Period = deferral time period  Payment = 0  FV = -Present value o Using the IRR formula on positive-negative-positive payment patterns o Cash flows that include a separate rate of return for borrowing (MIRR) o Cash flows that don’t follow a standard payment schedule (XIRR and XNPV)  Introduction to using SUMPRODUCT o SUMPRODUCT o How can we use SUMPRODUCT with conditional statements? Data Analysis -> September 22 , 2015 nd  Projects o The Society of Actuaries provides additional guidance on steps associated with conducting various types of analysis in Actuarial Science of Practice (ASOPs). o For a complete listing of ASOPs and to which line of work they apply, see:  Hypothetical Problem o Suppose you need to create a report based on sales figures. The person who supplies you with the data is new and does not understand what he or she has been asked. They send you a file that only contains 84% of the sales contracts drafted by the company. You run the study anyway to get a feel for sales behavior at your company. o Is working from partial data a problem? o Who is ultimately accountable for the data your study is based off? o ASOP 23: Data Quality provides guidance on how to handle data received from external sources.  Guidelines to performing analyses o 1. Identify what you’d like to know/learn/summarize  Be clear in identifying what you’d hope your analyses would accomplish  Identify the bare minimum that you’d need in order to perform your study.  Identify additional fields/information that may come in useful and why  Redundant data fields are okay (and may help later in the process)  Redundant fields are fields that can be calculated from other fields you possess.  Example:  Date of birth  Age  Age at purchase  Age at onset of claim  With DOB and various other data fields, you can calculate the ages. However, if “Age” is included in your source data, it’s best to pull it for comparison. Often one field may contain inaccuracies that the other field can detect or correct for. o 2. Ask for or pull out data you need  Using #1 for guidance, try to get as much data as possible  Don’t be afraid of redundant data fields, they may come in handy later  Identify what data is readily available vs. data that may take time to prepare  Know your time-frame; plan for unexpected delays  Estimate the size of the data in advance of receipt  Communicate your expectations o 3. Receive and review the data as it comes in  Is the data you received complete? Does it match initial estimates?  Is this enough for the analysis your trying to do?  Can the person who sent you the data also send you field definitions?  How was the data entered? What’s the likelihood of error?  Is there a “data scrub” process in place prior to your receiving the data?  If so, what would be changed? What set of rules are followed? o 4. Using #3, compare the data o the definitions  Identify errors within single fields  Use redundant fields to identify inconsistencies  Look for duplicates in your data o 5. Note inconsistencies and decide if you can move forward with analysis  Use Actuarial Standard of Practice #23 for decision guidance o 6. Perform analysis and include clear caveats in all summaries  Presentations, slides, etc. can be separated – avoid creating misleading exhibits  Data Sample o 1. Try to define the fields you received  Is this enough for the analysis you’re trying to do?  Can the person who sent you the data also send you the field definitions?  How was the data entered? What’s the likelihood of error?  What’s the likelihood of error in entering fields?  Is there a “data scrub” process in place prior to you receiving the data?  If so, what would be changed? What set of rules are followed?  Are there any redundant fields or fields with cross- dependencies?  Ex: Receiving both DOB and Age o 2. Using #1, compare the data to the definitions  Based on what you received, are there any issues?  Is the data set you received complete?  Prior to receiving a data file, try to estimate how many records you’d expect  Identify errors within single fields  Data Scrubbing o Definition:  This is performed to data prior to analysis. It can consist of simply verifying that all data is in the desired format, or it can consist of determining that the data is “bad” and needs to be corrected prior to initiating work on it. o Step 1: Define the fields of the Data set  What are you expecting to see in each entry?  Which fields are absolutely necessary for reporting?  What are all possible values of the fields chosen?  What is an acceptable margin of error?  What will you do with records that contain missing or incorrect data? o Step 2: Evaluate the dataset  Using definitions of fields above, determine if there are any outliers.  Try to determine the source of the errors.  If errors can be corrected, correct them and document  If there’s a common systematic error, create a process to clean the data, but also consider correcting the issue upstream.  Scrubbing also includes reformatting data.  Create or spill variables as deemed necessary. Midterm Potential Questions -> September 22 , 2015 nd 1. Which of the following functions below would return the value 250? a. =sumif b. =countif c. =index d. =vlookup e. =sumproducts 2. We identify a deck of cards with the numbers 1 through 52. To stimulate drawing a card at random from this deck, which excel function could be used? a. VLOOKUP b. INDEX c. RAND d. RANDBETWEEN e. IF 3. Which formula below would calculate the number of complete years between two dates in excel? a. =RANDBETWEEN(Date1, Date2) b. =DATEDIF(ROUND(DATE1/365,0) c. =DATEDIF(Date1,Date2,”Y”) d. =ROUNDUP(DATE2-DATE1)/365,0) th Access Intro -> September 29 , 2015  Access Ribbon Review o Home  Filter  Find  Refresh  Text formatting  Copy, paste, etc.  View: either design or datasheet o Create  Table & Table design  Query wizard & query design  Form & form design  Macros  Reports  Application parts o External Data  Imports  Exports o Database tools  Move data  Add ins  Run macro  Relationships  Object dependencies  What is Access for? o Excel based examples of where Access is a better fit  Access allows for linking of large tables that share some common fields  Access has a much higher size limitation than excel  Summarizing data is faster and more efficient through Access  Access can be used to link into even larger data sets (via SQL for example)  Access allows you to define fields so that memory is sued more efficiently  Data fields can be set up so that fewer data entry errors occur o Reasons to consider access  You need a location to input and store large quantities of data  You have multiple tables that need to be cross-references  You’re working on a multi-year data project and final size of data is unknown  Size and method of data storage is important o Reasons to still use excel  Table sizes are small (less than 10,000 rows and 50 total columns)  Data is entered in 1 singular table format  Many field calculations are required throughout the project  Familiarity of users with excel-based programs  What is a database o A database is a collection of tables, queries, reports, etc. o Like excel has multiple tabs, access allows multiple tables to be loaded  Building access tables o You are not limiting to building tables from scratch o You can:  Design and build from scratch (defining data elements first)  Use a pre-defined template and fill in data  Import data from various sources (including excel)  Link to external data sources o The difference between importing and linking  Importing is a one-time data upload  Linking creates an attachment to an external file o Why is linking useful?  Pros:  If you link to a file someone else updates frequently, you only need to refresh the link each time you open your database.  The alternative would be to re-import the file each time you use it.  Cons:  Queries based on linked tables will perform slower.  If you need a snapshot of the source data at a point in time, you would not get that with linking.  Field Types o When in design mode in access, you can choose among several field types: o Short Text  Allows text entry (number and letters) up to 255 characters in length o Long text  Allows for larger text entry (up to 65,353 characters in length) o Number  Digits only o Date/Time  Valid date and time entries o Currency  Same as number, with up to 4 decimal places and currency symbol o Auto number  A unique sequential number (automatically created when records are added) o Yes/No  Yes/no, true/false, or on/off entries o OLE Object  Any object that can be linked or embedded in a table o Hyperlink  Web link or file location link o Attachment  Pictures, charts, etc. o Calculated  Based on the calculation from other data fields in the same row o Lookup Wizard  Creates a drop down list from existing data or data you enter o With each field type, you can set up validation and formatting requirements using “Field Properties” and “Design View” o If you try to link 2 tables together using a common field, that common filed needs to be the same data type in both tables  Access Tables o The difference between importing and linking:  Importing is a one -time data upload, Creates a static file  Linking creates a connection to an external file, creates a table that can be refreshed th Access Queries -> October 6 , 2015  Query Types o Some additional table basics  Field Selection:  Complex field types can be added in the "Design View" menu. o In design view, type in a field name and select field type. o In the properties window, specify the additional criteria for the added field  Standard field types can often be added in “Datasheet view” o On the ribbon, select “Datasheet” from the “table tools” menu, then choose “new field”. o A list of standard field names and types will appear to the right  Fields: Building Tables  When Assigning field/building tables, always start with the lowest level of the field type you would need  Primary Key  Every table has at least one primary key  The primary key is a unique indicator of any given record within that table  In other words, you should not see the same entry listed for two different records wuthin a table if that field has been listed as a primary key  As we get into querying, you’ll start to see the important of primary keys in defining queries o Queries:  Query Types  Select o A "select" query will allow you to join tables and display results in a temporary data table. Select queries do not create a permanent table to work from, but the results can be sorted, copied, and pasted into other programs (like Excel). o I usually use Select queries for quick final views of a data set.  Make Table o As the name suggests, a "Make-Table" query will allow you to join tables and display results in a permanent table. o The benefit of running a make-table query is that the results are stored in a permanent table, which can then be readily available for use in other queries. o The drawback is that a permanent table is created, and so the new table will take up space in your database file. o Use a Make-Table query to create a final table that you may intend to reference often.  Append  Update o An "update" query will allow you to update a data field in your table. o For instance, if you added a "Member_Type" field into a table that contains member ages, and you want to populate with "Adult" or "Child" depending on whether the Age appears to be greater than 17, you can set up an update query to accomplish this task.  Crosstab  Delete  Query Help and Watch-outs o Queries can be either used to:  Link and pull specific data, row for row, or  Use “Totals: to reduce the number of rows in output  Group by is an excellent way to get a quick picture of your data set o Start of high level, and then move toward complex  Start with a total before complex to check if totals are correct o Understand the importance of a primary key  If you are linking two tables together using a common field, in one of those two tables, you should be dealing with a unique set of data  This is called one-to-one or one-to-many relationship  If you try to link two tables using a field that is not a primary key you end up with a many to many relationship, which will relist the same data for every combination of the linked data o Pay attention to how tour fields are defined when building, importing, and linking tables  Additional Notes o Ways to find duplicates  Use query wizard, all records will be shown where duplicate  Query in design view using aggregate table function o Ways to remove duplicates o Relationships (linking tables by specific fields) o Find unmatched query o Update query o Delete query o Crosstab query Book Notes -> October 6 , 2015th  Lesson 3 – Creating a new database application o The official website has many database templates that you can download o Make sure that the format, 2000, 2002-2003, or 2007, is appropriate for the functions o Creating a blank template  Select new  File new database to browse for desired location  Click create  Access will create a database with a single table with a single-ID field  Opened in datasheet view  Save the table  Close the database o Creating a database from template  Applications can be simple or complex  Northwind is a database available  Open access  Click sample templates, click Northwind  Click create  Lesson 4 – Access Database Objects o Access tables  Used to store data in the database application  Two kinds  System Tables o Required by access to manage the database file o Really just protected local tables in the database file o Read-only o Prefix MSys  User (Application) Tables o Local tables or linked o Created to store info needed for application  Web tables are specifically designed for use in conjunction with SharePoint  Include fields and indexes  Each field has a specific data type defined when the table is designed  Indexes speed up returning data  Each table must have a primary key  Primary key: establishes relationships with other tables  Autonumber data type is typically used to create o Access Queries  Select  Used to retrieve specific data from tables  Record source property for forms and reports  Can be restricted by applying criteria  Action  Generally used to modify the data in a table in one way or another  Append records to a table, update the field values in existing records, delete records, and create a new table from the records returned by a query  Data Definition  Can create, modify, or delete database objects, including tables, fields, and indexes in a database  Utilize Data Definition SQL  You can only create one table per query execution o Access Forms  Forms allow allow database application users to interact with the data and are much like other standard Windows application forms  Have properties, methods, and events that determine their overall characteristics  Property – stores a piece of data that determines a characteristic of the form  Events – used with specific code or macro actions when come predefined action type occurs  Methods – execute some built in or user defined functionality when explicitly called in macro or code  Form view  Form’s standard view where user interacts with form o Access Reports  Reports display data for users to see onscreen and print on paper  Provide grouping and sorting features for rendering data  Method for creating a visualization of the data stored in the database application  Report view o Access Macros  Common actions that are used in a database application, designed to be very easy for users to use  Macro designer  Embedded macro – stored in the code modules of a form/report and are called from an event method of that object  Macro database objects – commonly known as named macros, considered separate standalone database objects, designed to be called from other objects in the database  Data macros – can modify data in database  Places behind an event and are executed when an event is fired o VBA code in access  VBA – Visual Basic Applications  You can assign VBA cod to the object events and the code will execute when the event fires  VBE is used to write the VBA procedures  Lesson 7 – The Access 2010 Navigation Pane o Provides access to the various database objects o Used primarily for developing the database applications o Allows the developer to customize the views of the pane and how the database objects are grouped and displayed o Has predefined grouping settings  Can filter by a variety of options o can open objects in a variety of ways  clicking  enter  right click o Navigation Pane Context Menu  Right click on any object  Open, export, rename, hide, delete, cut, copy, paste, etc.  Can also right click on navigation pane’s title bar o Database Object Properties  View properties from context menu  Allows you to hide object  Gives some info o Navigation Options Dialog  Choose from context menu  Provides options to manage existing grouping options, create new groups, etc.  Search bar  Quickly filter  Show search bar in navigation options  Custom Groups  Edit groups in navigation pane  Creates shortcuts to objects  Hidden and System Objects  Only expose what is needed  Hide in navigation pane  Lesson 8 – Creating a Table in Datasheet View o Basics  Made up of sets of fields (columns) specific data types  Store data in these fields in the form of individual records (rows) o Create ribbon offers three basic methods for creating tables  Table button – creates an opens a new table in Datasheet View  Table design – opens the table in design view and is traditional  Sharepoint lists button – create linked tables o Terms  Relational database – creates databases with a collection of tables that often link dimensions of data in one table to others  Primary Key – table field that contains unique values that access uses to identify the record  Natural Key – values that are derived from data being compiled and stored  Natural Compound Key – multiple fields can be combined to form  Surrogate Key – values created by the database that have no meaning outside of database  Autonumber  Foreign key – field in a table that contains values from a primary key field in the related table o Naming Schemes  Be consistent  Camel case o Creating a table in datasheet view  Click table  Add fields  Change data type  Save o Table Tools Ribbon  Shown when table is opened o Adding new fields to a table  Add & delete  More fields o Setting data types  Variety of data types o Application parts  Create relationship wizard o Sharepoint  Has a variety of pre-made templates  Lesson 9 – Creating a table in design view o Click design view  Creates a grid with field name, data type, description and general/lookup at bottom o Table Tools Design Ribbon  Views:  Datasheet view  Pivot table view  Pivot chart view  Design view  Tools:  Primary key  Builder  Test validation  Insert rows  Delete rows  Modify lookus o Starts lookup wizard  Show/Hide  Various things to show or hide  Lesson 10 – Access 2010 Data Types o Text  255 characters o Memo fields  A lot of characters o Number fields  Byte < integer < long  Single and double are floating point o Date/time  A number would translate to number of days or microseconds from set date o Currency fields  15 to the left and 4 to the right o AutoNumber Fields  Provides a integer number starting at 1 o OLE Object Fields  Stores linked or embedded  Causes the data to grow in size o Hyperlink  URL o Attachment Fields  Similar to email attachments o Calculated fields  Underlying expression with value returned o Multivalued fields  Lists o Lookup fields  Related to fields in another  Lesson 11 – Table and Field Properties o Table property sheet can be activated when opened in design view o Setting properties  Click button and add a value o Properties:      Lesson 18 – Creating Tables from External Data o External data tab o Import spreadsheet wizard  Lesson 19 - Creating Linked Tables o Linked Table in access  Two tier applications, front end that use linked tables o Linked table to other access databases  Can only link tables  Click access button on external data ribbon  Browse to database you want to link  Choose option link  Select tables you want to link o Linked tables to excel  Excel is read-only  Excel button in import group on external data ribbon  Similar to access o Linked tables to SharePoint  Click more button on import and link  Choose sharepoint list  Enter credentials  Select lists  Test link when done th Book Notes -> October 13 , 2015  Lesson 15 – Table Modifications o Renaming a table  Name autocorrect feature and the object dependencies help the user from making mistakes  To change the name of a table  Close the database and make a complete backup of the file  Open the database  In navigation pane, right click the table you want to rename  Select rename  Type in new name  Press enter key  Update name in any related objects if autocorrect is not enabled  Test the other objects o Table Locking Considerations  There is a chance of conflicts arising when more than one user is attempting to simultaneously edit the same record  Locking schemes can prevent or reduce the chances of multiple users editing the same record simultaneously  Go to advanced section of the Client Settings tab of the Backstage and choose one of the three locking options  No Locks o Optimistic locking o Gets a warning  All Records o Only the first user can make edits o All records are locked o Other users can see in read-only mode  Edited Records o Pessimistic locking o A page of records is locked o Remainder of the records are available for other users to edit o Modifying Fields  Same as changing a table name o Data Integrity Considerations  Be careful when changing data type  Lesson 16 – Creating Table Relationships o Must be similar items o Right click on arrow to change properties  Lesson 17 – Creating Table Field Indexes o Unique – will not allow duplicates o Or may allow duplicates  Lesson 21 – Creating Queries with the Wizard o Notes in class  Lesson 22 - Creating Queries in Design View o Notes in class  Lesson 23 – Creating Select Queries o Criteria uses >,<,=,AND o Can use functions  Lesson 24 – Data Aggregation and Grouping o Group by  Lesson 26 – Creating Append Queries o General Info  Append queries are used to add a row or multiple rows into table  Multi Record Append Query  Uses multiple records that originated from pre- existing tables  Single record append query  Gets its values from fields that are either literal values or calculated values o Creating an append query in design mode  Create a select query  Click append  Set append to name  insert the appended row/rows  Lesson 27 – Creating Update Queries o Create a select query o Click update query o Enter criteria using the following format  [parent].[kid].[kid].[kid]  Lesson 28 – Creating Delete Queries o Creating a delete query in design mode  Make a backup copy of the file  Open database  Click query design and create a new query  Write a select query  Click delete query button  Click run  Verify that you want it to run  Lesson 29 – Creating Make Table Queries o Creating a Make Table Query in design mode  First create a select query to use as the source  Click make table button in query type section  Type a name or select a name  Choose database  Run table o Make Table Query Settings  Can be based on single tables or multiple tables  Many have to be made through the query o Creating Make a Table in SQL view  Select  Into  From o Ramifications  Relatively safe  Lesson 30 – Creating Crosstab Queries o Overview  A crosstab query will transform the records of data from a table into columns in a new table  Can generate a result set of aggregate data in a table typically in the form true/false values  Can also merge two different sets of records into a new table containing some data aggregation for those records o Cross Tab Query Wizard  Helps the user to create a Crosstab query  Click query wizard  Select crosstab as an option  Select up to three fields as row heading and one for column heading  If date field is selected, you can choose an option to group the fields  Select the aggregate field o Creating a crosstab query in design mode  Multiple tables or queries can be added to the crosstab query  Eliminates single tab restriction  Select total function and crosstab function o Crosstab Query Settings  Parameters must be explicitly stated using the PARAMETERS clause  SQL view  Query Parameters dialog  IN(All possible column headings property) o Creating a crosstab query in SQL view  Allows you to write the specific SQL statement you wish to use for the query  Provides greatest flexibility  Constructed from the following SQL statement  Parameters o Explicitly defines the query parameters  Transform o Specifies the field used as the Value field and the aggregate function to be used  Select o Lists the field used as row headings  From o Joins to multiple tables or queries are optional. Specifies table to use  Where o Optional o Any criteria  Group By o The non-aggregated Row header fields in the select clause  Pivot o Specifies column header field Intro VBA -> October 15, 2015  Excel File Type Information o *.xlsx  used for saving spreadsheets w/ no macro functionality  default format for saving Excel files o *.xlsm  used for saving spreadsheets w/ macro content  any VBA projects made in the class should be saved in this format or *.xlsb  don’t just hit save o *.xlsb  excel binary format, allows for larger, more complex excel spreadsheets to be saved, would function poorly in xlsx  >10MB  also support VBA code o *.xltx / *xltm  excel template files  if you save projects in a specific format type you can set up a template file that allows you to start with a customized file o *xlam  This is how you would save a custom made add-in for excel  Add-ins are files with only code, no spreadsheets  VB Editor o Do you have a developer tab?  If no:  File -> options -> customize ribbon  Click on file tab  Under customize the ribbon and under main tabs select the developer check box o Getting into VB Editor  Developer -> Code -> Visual Basic o Requiring Variable Declaration  Go to VB Editor  Tools -> Options -> Editor  Make sure “Require Variable Declaration” is checked o Views in the VB Editor  Project Window: Displays a tree diagram of open windows (including Add-ins & hidden workbooks)  Code Window: contains VBA code for a selected object or module  Properties Window: Contains a list of properties for a selected object. (this varies by the object type)  Immediate Window: This is useful for executing / testing / debugging in VBA  Code Basics o In VBA there are 3 types of Code  Sub (including Macros)  A procedure is a set of instructions that performs some action.  Sub procedures is a set of code that you can execute.  All “recorded macros” are Subs  Function procedures  A function is a set of instructions, and will return a single value or array output.  Functions will not alter your workbook  Declarations  Public/Private/ Dim  These are variables you intend to use in your routines  By declaring variables, you can modify the space requirements and improve processing time o Types of Variables  Boolean  Returns 1 or 0 (True or False)  Stores a very small amount of data  Use this only with true/false variables  Integer  A data type that holds integer variables stored as 2- byte whole numbers in the range -32,768 to 32,767  Long  A 4-byte integer ranging from -2 billion to 2 billion  Currency  $$$  Single  Single precision floating point number  Double  Double precision floating point number  Date  Left of decimal is date, right of decimal is time  String  Characters  Objects  Not a variable type so much as the kinds of things that you manipulate  Some objects in VBA may be worksheets, ranges, files  Variant  Don’t know what your variable will be, but must declare it  This is the default variable type  Debugging o Several Options for debugging  Use MsgBox (message) command  This will allow you to have messages pop up while the code is running  Debugging toolbar – setting watches, stepping through  This can check many variables as the code runs and you can control it step-by-step  Set watches (to see variable values)  Set breakpoints to stop flow at specific steps  Create a Sub Procedure to call the function  From here, you can also use the “stop sign” feature in your code  Custom Functions o Step 1: Declare the Function [Public | Private] [Static] Function name ([arglist]) [As Type] [code commentary] [statements] [name = expression] [Exit Function] [statements] [name = expression] End Function [bracketed] = Optional Inputs Italicized = Optional, user­defined items [code commentary] ­ should be used inbetween lines of actual code. Any statements that start with a leading ' will be distregarded and therefore you can document your code easily. o Step 2: Determine if passthrough variables are required  A passthrough variable is an input  In this case, we want to input a cell or something to identify the current worksheet o Step 3; Write your code  I wouldn’t recommend writing more than 5 to 10 lines of code at a time without testing it  There are many ways to “test” your code o Step 4: Test your code  Once everything is complete, test  If you get the #VALUE error you’ll want to go to the debugging section VBA Book Notes -> October 25, 2015  Lesson 1 – Introducing VBA o Visual Basic for Applications (VBA) is a programming language created by Microsoft to automate operations in applications o Automate a recurring task or repetitive task o Run a macro automatically if another action takes place o Create your own function o Simplify workbook look for others o Control other office apps from excel  Lesson 2 – Getting Started with Macros o Make sure that the developer bar is on o Record a macro, perform task, end recording  Lesson 3 – Introducing the Visual Basic Editor o Alt F11 o Similar to MATLAB  Lesson 4 – Working in the VBE o ‘ means comment o Sub begins o End Sub ends o Similar to MATLAB VBA Notes -> November 3, 2015  Calculations o Formulas in VBA  On the LEFT of the equal sign is the variable you’ll be overwriting  On the RIGHT of the equal sign are the calculations to be performed o Calculations in VBA  All mathematical calculations are pretty much the same  Also:  xMODy -> if you divide x/y, this returns the remainder, statusbar  ROUND -> similar to “ROUND” spreadsheet function  INT -> returns the integer portion of a number  RND -> same as “RAND()” spreadsheet function  for more  Some VBA functions perform different from Excel, ex. ROUND  If there is a calculation that you need to perform often you can write a function in VBA  Variables o Public  Coded outside the sub  If a value is stored it will carry that value through into all other subs o Dim  Set up inside the sub  Defines the variable just for the current sub  Any reference outside the sub will be null o Pass-Through  Variables can be defined directly in the function or in the sub command  If Statement o Similar to MATLAB o End with “End If”  CASE Selection o Similar to else but use Select Case, Case, End Select  Loops o Loop: a segment of code that will run as long as a condition criteria is met. When coding loops or statements, please use indenting as a way of properly documenting what is going on. o For  Most common  For ___ this will run o While  As long as _____ is true the code will run o For Each object in Objects / Next Object  Cycle through all of a particular type of object and perform actions  Objects o Application  Is the general coding reference for the application you are using o Application.ActiveSheet  The current sheet you are on o Application.DisplayAlerts = [True or False]  Use this to prevent pop-ups from occurring during your code o Application.ScreenUpdating = [True or False]  Use this to prevent the screen from flashing/blinking while running code o Application.StatusBar  Use this to communicate with the user during code execution o Application.WorksgeetFunction  Use this to call on a specific worksheet function  This doesn’t list all worksheet functions available in excel, but many  This way you don’t have to create code for something that excel can already do o Sheets(“sheet_name”)  Use this to reference a specific worksheet o Application.Calculation=  Turn off automatic calculation while performing a macro to prevent code running slowly (with large workbooks) o Range(“named_range”)  Use this to work with a range within a workbook o Select vs. Activate  Select: Is used to choose several items at once. You will see “select” used when you choose a range of cells  Activate: is used to choose and highlight just one item. You will see “activate: when you want to highlight one cell or sheet  Arrays o You can specify variables to hold more than one values November 12 Class Notes  Loop Commands o Do While -> Loop o For -> Next


Buy Material

Are you sure you want to buy this material for

75 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Jennifer McGill UCSF Med School

"Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.