Economic data analysis with Microsoft Excel and VBA I
|Kod Erasmus / ISCED:||
|Nazwa przedmiotu:||Economic data analysis with Microsoft Excel and VBA I|
|Jednostka:||Wydział Nauk Ekonomicznych|
Anglojęzyczna oferta zajęć WNE UW
Przedmioty kierunkowe dla Data Science
Przedmioty kierunkowe do wyboru - studia II stopnia IE - grupa 1 (6*30h)
Przedmioty kierunkowe do wyboru- studia I stopnia EP
Przedmioty wyboru kierunkowego dla studiów licencjackich FIM
Przedmioty wyboru kierunkowego dla studiów licencjackich IE
|Punkty ECTS i inne:||
Many people who work in a spreadsheet every day use only its very basic functions, treating it as a more extensive calculator. The course aims to teach participants how they can automate their everyday work in MS Excel by using tools such as related pivot tables, advanced features of conditional formatting and, above all, macro commands. Effective use of a spreadsheet is based on automating our work in order to save the precious time that is wasted on performing routine activities.
Learning by Doing is the motto of the course. Students have to solve a new task for each class, constructed in a way that consolidates the previously acquired skills and learn new issues. Numerous examples of applications discussed in classes will facilitate their later use at work.
Microsoft Excel is the most widely used program for data analysis and presentation. Unfortunately, it is often used in a very inefficient way. Excel contains all the necessary elements for processing economic data. It was created for office and economic applications, thanks to which there are no restrictions as to the scope of statistical and financial applications. Of course, there are numerous econometric or financial programs in which the calculation of advanced models is much easier. However, firstly companies in the world are often unable to afford very expensive econometric programs, which is why we are often "convicted" to Excel, and secondly some tasks are much easier and faster with Excel.
This course was created as a result of many years of observation of people who use Excel on a daily basis. Very often people, even those who know the program well, work inefficiently devoting a lot of time to tasks that can be easily automated. The motto of this course is: do not waste your precious time on routine, repeated daily activities. It's better to work once creating an application that will, for example, import data from the internet into a spreadsheet, make a graphical and statistical report, print the results, or send them to the boss by e-mail, than repeat the same activities every day.
The course is designed for people who work in a spreadsheet and would like to learn how to write macro commands - VBA. VBA is a programming language and 75% of classes are aimed at learning the language. Excel will be only a place where we will observe the results of our work.
Detailed subject of the course:
• import of source data - import of external data from text files, databases (including Access) and from the network (www);
• preparation of data for analysis - advanced text functions, search and address functions, conditional counting.
• defining dynamic ranges using functions;
• creating advanced reports using pivot tables;
• advanced data analysis using array functions;
• complex conditional formatting using proprietary formulas;
• data validation - types of conditions, determination of input messages and error alerts;
• creating user-friendly sheets;
• introduction to macros - what are the macro commands and how to handle them, registering simple macros;
• macro recording - code editing, its construction and optimization;
• writing own macros: defining variables (including array variables), conditional statements (If ... Then ... Else), loops (To ... Loop), instructions (For ... Next), dialog boxes;
• Sub-procedures - scope of procedures; calling procedures; arguments of procedures; practical examples of Sub type procedures;
• Function type procedures - argumentless, unary, multi-argument functions, with optional arguments, with an undefined number of arguments, array functions, insertion of a function description in Excel;
• creating charts and pivot tables from the VBA level;
• Case study: an application that imports data on sales reports from many stores to Excel, consolidates all reports, creates reports for individual product groups and saves them in separate files.
Note 1: this course requires a lot of work from students. Classes are conducted at an advanced level.
Note 2: classes are held in the form of e-learning on the platform: https://moodle.wne.uw.edu.pl (no live classes).
Note 3: The software version in the course is Microsoft Office 2019 or 365 (language version: English, operating system: Windows). Using an earlier version is possible, but it may cause some difficulties. The Polish language version is not a major problem, but the course a different operating system is (e.g. MacOS) due to the limitations of the Office suite (lack of necessary software components). The University of Warsaw does not provide participants with the necessary computer hardware or software.
• own materials
• Walkenbach J., Excel; 2019 Bible. Wiley 2019
• Jackson M., Staunton M., Advanced modelling in finance using Excel and VBA. Wiley 2001
• Walkenbach J. Excel 2019 Formulas. Wiley 2019
• Jelen B., Syrstand T., Excel 2019 VBA and Macros. Pearson Education 2019
• Alexander M., Power Programming with VBA, John Wiley & Sons Inc 2016
|Efekty uczenia się:||
The student will gain the ability to efficiently use the VBA language and the ability to use it to create applications in MS Excel. Will be able to use advanced programming methods in VBA, create business reports, as well as write custom functions in Excel.
Students who complete the courses "Economic data analysis with Microsoft Excel and VBA I" and "Excel applications in the enterprise using the language VBA part 2" with grades at least very good, will receive special certificates confirming the acquired knowledge and skills. Such a certificate may constitute a valuable item in your CV! Note: the second level of the course is currently only in Polish - it is planned to open the course in English.
|Metody i kryteria oceniania:||
The final evaluation consists of:
• three collective housework, each scoring 40 points;
• extra points for activity.
from 110 5!
Właścicielem praw autorskich jest Uniwersytet Warszawski, Wydział Nauk Ekonomicznych.