Programming For Actuaries All Notes
Programming For Actuaries All Notes MATH 3550
Popular in Programming for Actuaries
Popular in Mathematics (M)
verified elite notetaker
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.
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 http://office.microsoft.com/en-us/excel/HP011116591033.aspx 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: http://www.actuarialstandardsboard.org/pdf/contents.pdf 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, userdefined 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 Techonthenet.com 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
Are you sure you want to buy this material for
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'