def get_lab_exercise() -> LabExercise:
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
next_slide = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
df_to_excel_example = pl.Python(
"df.to_excel('data.xlsx', sheet_name='My Data', index=False)")
df_from_excel_example = pl.Python(
"df = pd.read_excel('data.xlsx', sheet_name='My Data')")
index_false_mono = pl.Monospace('index=False')
addin_install_mono = pl.Monospace('xlwings addin install')
addin_install_success = pl.Monospace(
'Successfuly installed the xlwings add-in! Please restart Excel.')
random_seed_py = pl.Monospace('random_seed.py')
random_seed_excel = pl.Monospace('random_seed.xlsm')
quickstart_mono = pl.Monospace('xlwings quickstart')
quickstart_project_mono = pl.Monospace(
'xlwings quickstart my_project_name')
cd_mono = pl.Monospace('cd')
xw_func_decorator = pl.Monospace('@xw.func')
random_choice_mono = pl.Monospace('random_choice')
random_choices_mono = pl.Monospace('random_choices')
random_choice_py = pl.Monospace('random.choices')
xlwings_mono = pl.Monospace('xlwings')
df_returns_example = pl.Monospace("df['Stock Price'].pct_change()")
bullet_contents = [
[[
'In your', xlwings_mono,
'Python file, add a function which will generate 1, 2, ... N in a column for',
'whatever N is passed.'
],
[
'Add a cell in your wookbook containing the number of rows to be generated, and call your UDF in',
'another cell.'
],
[
'Change the number of rows to be generated and see the number of rows in the output change.'
]],
[[
'In your', xlwings_mono,
'project workbook, add a column Stock Price which has five prices below it,',
'100, 110, 115, 108, and 105.'
],
[
'In the Python file, write a UDF, which accepts the top-left cell of this table (the one',
'with Stock Price), and returns the stock returns.'
],
[
'Call this UDF next to the stock price column so that you have the return from last period to this one',
'next to the stock prices.'
], ['You may find', df_returns_example, 'useful for this purpose.'],
['Answers should be 10%, 4.5%, -6%, -2.8%']]
]
return LabExercise(bullet_contents,
'Advanced UDFs',
f"Dynamic and Array UDFs with {xlwings_mono}",
label='lab:dynamic-array-udf')
def get_content():
random.seed(1000)
next_until_end_ov = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
next_slide = lp.Overlay([lp.NextWithIncrement()])
numpy_mono = pl.Monospace('numpy')
lecture = get_dynamic_salary_python_lecture()
return [
pl.Section(
[
lp.DimRevealListFrame(
[
'We have seen how structure and organization can help the readability and maintainability of '
'an Excel model. The same concept exists for our Python models.',
['We already learned that we should use functions to organize logic and a',
pl.Monospace('dataclass'),
'for the model inputs'],
"Typically you'll have functions for each step, those may be wrapped up into other functions which "
"perform larger steps, and ultimately you'll have one function which does everything by calling the"
"other functions.",
"Those are good ideas with any Python model, but working in Jupyter allows us some additional "
"organization and presentation of the model"
],
title='How to Structure a Python Financial Model'
),
lp.DimRevealListFrame(
[
'In Jupyter, we can have code, nicely formatted text, equations, sections, hyperlinks, '
'and graphics, all in one document',
['For all you can do with these nicely formatted "Markdown" cells, see',
Hyperlink('https://www.markdownguide.org/basic-syntax/', 'here'), 'and',
Hyperlink('https://www.markdownguide.org/extended-syntax/', 'here.')],
'We can think of sections in Jupyter as analagous to Excel sheets/tabs. One section for each '
'logical part of your model. Then you can have smaller headings for subsections of the model.',
'Break your code up into small sections dealing with each step, with nicely formatted text '
'explaining it. Add comments where anything is unclear in the code.',
],
title='Using Jupyter for Structure of a Model'
),
lp.GraphicFrame(
[
get_model_structure_graphic()
],
title='Structuring a Python Model'
),
lp.DimRevealListFrame(
[
'When I develop in Jupyter, I have lots of cells going everywhere testing things out',
['When I finish a project in Jupyter, I remove these testing cells and make sure it runs and '
'logically flows from end to end', pl.Monospace('(restart kernel and run all cells)')],
"Run your model with different inputs, and make sure the outputs change in the expected fashion. This "
"is a good way to check your work.",
'There may be outputs in each section, but the final output should be at the end of the notebook',
],
title='Workflow and Final Output'
),
],
title='Structuring a Model in Python and Jupyter',
short_title='Model Structure'
),
pl.Section(
[
lp.DimRevealListFrame(
[
'The first project is aimed at approaching a new time value of money and cash flow model',
'It covers the same concepts as the retirement model, but in a capital budgeting setting',
'We need to introduce some economic equations to handle this model. You should have covered these in microeconomics.'
],
title='Introducing Project 1'
),
lp.GraphicFrame(
images_path('supply-demand-graph.png'),
title='A Quick Review of Supply and Demand'
),
lp.Frame(
[
lp.Block(
[
"There are a couple of basic economic equations we haven't talked about that "
"we'll need for this:",
pl.Equation(str_eq='R = PQ', inline=False),
pl.Equation(str_eq='Q = min(D, S)', inline=False),
pl.UnorderedList([
f'{pl.Equation(str_eq="R")}: Revenue',
f'{pl.Equation(str_eq="Q")}: Quantity Purchased',
f'{pl.Equation(str_eq="D")}: Quantity Demanded',
f'{pl.Equation(str_eq="S")}: Quantity Supplied',
])
],
title='New Required Equations'
)
],
title='Equations for Project 1'
),
lp.Frame(
[
pl.UnorderedList([
lp.DimAndRevealListItems([
'We need to cover one more Python concept and one gotcha before you can complete the first project.',
"On the next slide I'll introduce error handling, and show an example of how it's useful"
],
vertical_fill=True)
]),
lp.AlertBlock(
[
pl.UnorderedList([
f'The NPV function in {numpy_mono} works slightly differently than the NPV function in Excel.',
f'Excel treats the first cash flow as period 1, while {numpy_mono} treats the first cash flow as period 0.',
'If taking NPV where the first cash flow is period 1, pass directly to Excel, and for Python, pass 0 as the first cash flow, then the rest.',
'If taking NPV where the first cash flow is period 0, pass from period 1 to end to Excel and add period 0 separately, pass directly to Python.'
])
],
title='NPV Gotcha',
overlay=next_slide
)
],
title='A Couple More Things on the Python Side'
),
],
title='Project 1 Additional Material',
short_title='Project 1'
),
pl.Section(
[
get_retirement_model_overview_frame(),
lp.Frame(
[
lp.Block(
salary_block_content,
title='Salary with Promotions and Cost of Living Raises'
)
],
title='Revisiting the Model Salary Equation'
),
lp.Frame(
[
pl.UnorderedList([
'For wealths, we need to add the investment return and then the savings in each year',
], overlay=next_until_end_ov),
pl.VFill(),
lp.Block(
[
lp.adjust_to_full_size_and_center(
pl.Equation(str_eq=r'W_t = W_{t-1} (1 + r_i) + S_t v')),
pl.UnorderedList([
f'{pl.Equation(str_eq="S_t")}: Salary at year {pl.Equation(str_eq="t")}',
f'{pl.Equation(str_eq="W_t")}: Wealth at year {pl.Equation(str_eq="t")}',
f'{pl.Equation(str_eq="r_i")}: Investment return',
f'{pl.Equation(str_eq="t")}: Number of years',
f'{pl.Equation(str_eq="v")}: Savings rate',
])
],
title='Calculating Wealth',
overlay=next_until_end_ov,
)
],
title='Building the Wealth Model'
),
InClassExampleFrame(
[
'I will now show the process I use to create a full model.',
'I will be recreating the model "Dynamic Salary Retirement Model.ipynb"',
'Go ahead and download that to follow along as you will also extend it in a lab exercise',
],
title='Creating a Full Model in Python',
block_title='Dynamic Salary Retirement Model in Python',
),
get_extend_dynamic_retirement_python_lab_lecture().to_pyexlatex().presentation_frames(),
LabExercise(
[
[
"Usually I would try to have smaller labs but it didn't fit the format of this lecture. "
"Most will not be able to complete this during class.",
"For this lab, attempt the practice problem "
'"P1 Python Retirement Savings Rate Problem.pdf"',
'This is similar to how the projects will be assigned, so it is good preparation',
"I would encourage you to try it from scratch. If you are totally stuck, try working off "
"of the retirement model I completed today to have a lot of the structure already. If you "
"still are having trouble with that, check the solution and see me in office hours.",
'Note: this is not an official lab exercise you need to submit, it is practice only, but '
'I would highly encourage you to complete it.'
]
],
block_title='Practice Building A Model',
frame_title='Extending the Simple Retirement Model in a Different Way',
label='lab:retire-model'
),
],
title='Building the Dynamic Salary Retirement Model',
short_title='Build the Model'
),
pl.PresentationAppendix(
[
lecture.pyexlatex_resources_frame,
get_extend_dynamic_retirement_python_lab_lecture().to_pyexlatex().appendix_frames(),
]
)
]
def get_content():
lecture = get_python_basics_lecture()
conditionals_lab = get_python_basics_conditionals_lab_lecture().to_pyexlatex()
lists_lab = get_python_basics_lists_lab_lecture().to_pyexlatex()
functions_lab = get_python_basics_functions_lab_lecture().to_pyexlatex()
data_types_lab = get_python_basics_data_types_lab_lecture().to_pyexlatex()
classes_lab = get_python_basics_classes_lab_lecture().to_pyexlatex()
appendix_frames = [
*conditionals_lab.appendix_frames(),
*lists_lab.appendix_frames(),
*functions_lab.appendix_frames(),
*data_types_lab.appendix_frames(),
*classes_lab.appendix_frames()
]
next_slide = lp.Overlay([lp.NextWithIncrement()])
function_example = pl.Python(
"""
def my_func(a, b, c=10):
return a + b + c
>>> my_func(5, 6)
21
""")
use_class_example = pl.Python(
"""
from car_example import Car
>>> my_car = Car('Honda', 'Civic')
>>> print(my_car)
Car(make='Honda', model='Civic')
>>> type(my_car)
car_example.Car
>>> my_car.make
'Honda'
>>> my_car.drive()
'The Honda Civic is driving away!'
""")
dataclass_example = pl.Python(
"""
from dataclasses import dataclass
@dataclass
class ModelInputs:
interest_rates: tuple = (0.05, 0.06, 0.07)
pmt: float = 1000
>>> inputs = ModelInputs(pmt=2000)
>>> print(inputs)
ModelInputs(interest_rates=(0.05, 0.06, 0.07), pmt=2000)
>>> type(inputs)
__main__.ModelInputs
>>> inputs.interest_rates
(0.05, 0.06, 0.07)
>>> inputs.pmt
2000
""")
if_example = [pl.Python(
"""
>>> if 5 == 6:
>>> print('not true')
>>> else:
>>> print('else clause')
>>>
>>> this = 'woo'
>>> that = 'woo'
>>>
>>> if this == that:
>>> print('yes, print me')
>>> if this == 5:
>>> print('should not print')
"""
), pl.Monospace('else clause'), OutputLineBreak(), pl.Monospace('yes, print me')]
build_list_example = pl.Python(
"""
>>> inputs = [1, 2, 3]
>>> outputs = []
>>> for inp in inputs:
>>> outputs.append(
>>> inp + 10
>>> )
>>> outputs.insert(0, 'a')
>>> print(outputs)
['a', 11, 12, 13]
"""
)
enumerate_example = [pl.Python(
"""
>>> inputs = ['a', 'b', 'c']
>>> for i, inp in enumerate(inputs):
>>> print(f'input number {i}: {inp}')
"""
),
pl.Monospace('input number 0: a'),
OutputLineBreak(),
pl.Monospace('input number 1: b'),
OutputLineBreak(),
pl.Monospace('input number 2: c')
]
list_indexing_example = pl.Python(
"""
>>> my_list = ['a', 'b', 'c', 'd']
>>> my_list[0] # first item
'a'
>>> my_list[1] # second item
'b'
>>> my_list[-1] # last item
'd'
>>> my_list[:-1] # up until last item
['a', 'b', 'c']
>>> my_list[1:] # after the first item
['b', 'c', 'd']
>>> my_list[1:3] # from the second to the third item
['b', 'c']
"""
)
f_string_example = pl.Python(
"""
>>> my_num = 5 / 6
>>> print(my_num)
0.8333333333333334
>>> print(f'My number is {my_num:.2f}')
'My number is 0.83'
"""
)
f_string = pl.Monospace("f''")
f_mono = pl.Monospace('f')
try_except_example = pl.Python(
"""
>>> my_list = ['a', 'b']
>>> try:
>>> my_value = my_list[10]
>>> except IndexError:
>>> print('caught the error')
caught the error
"""
)
list_100_5 = pl.Monospace('[100] * 5')
next_slide = lp.Overlay([lp.NextWithIncrement()])
annuity_example = pl.Python(
"""
>>> annuity = [100] * 5
>>> annuities = [
>>> annuity,
>>> [0, 0, 0] + annuity
>>> ]
>>> n_years = 10
>>> output = [0] * n_years
>>> for i in range(n_years):
>>> for ann in annuities:
>>> try:
>>> output[i] += ann[i]
>>> except IndexError:
>>> pass
>>> print(output)
[100, 100, 100, 200, 200, 100, 100, 100, 0, 0]
"""
)
site_link = Hyperlink(SITE_URL, 'the course site')
return [
pl.Section(
[
lp.TwoColumnGraphicDimRevealFrame(
[
"Now we are going to build our first complex Python model",
"We will also learn a bit more Python before we can get there",
"Just as we did in Excel, we need to add structure to make the model navigatable",
"Logic should be organized in functions and be documented",
],
graphics=[
images_path('python-logo.png')
],
title='An Organized Structure of an Advanced Python Model'
),
lp.GraphicFrame(
[
get_model_structure_graphic()
],
title='The Structure of a Complex Model'
),
],
title='Introduction',
short_title='Intro'
),
pl.Section(
[
lp.Frame(
[
lp.Block(
[
pl.TextSize(-1),
if_example,
],
title='If Statements in Python'
),
],
title='Python Conditionals - If Statement'
),
lp.DimRevealListFrame(
[
'Use two equals signs to compare things (single to assign things)',
'Else is equivalent to value if false behavior in Excel',
'We can do a lot more than just set a single value, anything can be done in an if or else statement',
[pl.Monospace('elif'),
' is a shorthand for else if, e.g. not the last condition, but this condition']
],
title='Explaining the If-Else Statements'
),
InClassExampleFrame(
[
f"On {site_link}, there is a Jupyter notebook called Python Basics containing all "
f"of the examples for today's lecture",
'Now I will go through the example material under "Conditionals"'
],
title='Conditionals Example',
block_title='Trying out Conditionals'
),
conditionals_lab.presentation_frames(),
],
title='Conditionals'
),
pl.Section(
[
lp.Frame(
[
lp.Block(
build_list_example,
title='List Building'
),
pl.UnorderedList([
lp.DimAndRevealListItems([
['Use ', pl.Monospace('.append'), ' to add an item to the end of a list'],
['Use ', pl.Monospace('.insert'), ' to add an item at a certain position'],
])
])
],
title='Python Patterns - Building a List'
),
lp.Frame(
[
pl.UnorderedList([
'Index is base zero (0 means first item, 1 means second item)',
]),
pl.VFill(),
list_indexing_example,
],
title='List Indexing and Slicing'
),
InClassExampleFrame(
[
"We will keep working off of Python Basics.ipynb",
'Now I will go through the example material under "Working more with Lists"'
],
title='Lists Example',
block_title='Doing More with Lists'
),
lists_lab.presentation_frames(),
],
title='More with Lists',
short_title='Lists'
),
pl.Section(
[
lp.DimRevealListFrame(
[
"In Python, we can group logic into functions",
"Functions have a name, inputs, and outputs",
"Functions are objects like everything else in Python",
function_example,
],
title='Functions - Grouping Reusable Logic'
),
InClassExampleFrame(
[
"We will keep working off of Python Basics.ipynb",
'Now I will go through the example material under "Functions"'
],
title='Functions Example',
block_title='Structuring Code using Functions'
),
functions_lab.presentation_frames(),
],
title='Functions'
),
pl.Section(
[
lp.DimRevealListFrame(
[
'In Python, everything is an object except for variable names, which are references to objects',
'Every object has a type. We have learned about strings, numbers, lists, and booleans (True, False)',
'In the next section on classes, we will learn more about the relationship between the type '
'and the object'
],
title='What are Types?'
),
# TODO [#12]: add f-strings to Jupyter example
lp.Frame(
[
pl.UnorderedList([
lp.DimAndRevealListItems([
'You may have noticed that we can end up with a lot of decimals in Python output',
'Further, you may want to include your results as part of a larger output, such as a sentence.',
f'For these operations, we have {f_mono} strings: {f_string}'
],
vertical_fill=True)
]),
lp.Block(
f_string_example,
title='Example',
overlay=next_slide
)
],
title='Formatting Python Strings'
),
lp.DimRevealListFrame(
[
'So far I have just said that numbers are a type in Python, but this is a simplification',
['There are two main types of numbers in python:', pl.Monospace('float'), 'and',
pl.Monospace('int'), 'corresponding to a floating point number and an integer, respectively'],
['An', pl.Monospace('int'), 'is a number without decimals, while a', pl.Monospace('float'),
'has decimals, regardless of whether they are zero'],
['For example,', pl.Monospace('3.5'), 'and', pl.Monospace('3.0'), 'are floats, while',
pl.Monospace('3'), 'is an int, even though', pl.Monospace('3.0 == 3 is True')],
["Usually, this doesn't matter. But to loop a number of times, you must pass an",
pl.Monospace('int')]
],
title='Numeric Types'
),
lp.DimRevealListFrame(
[
['A', pl.Monospace('tuple'), 'is like a', pl.Monospace('list'), "but you can't change it after "
"it has been created (it is immutable)"],
['Tuples are in parentheses instead of brackets, e.g.', pl.Monospace('("a", "b")')],
['A', pl.Monospace('dict'), 'short for dictionary, stores a mapping. Use them if you want '
'to store values associated to other values'],
['We will come back to', pl.Monospace('dicts'), 'later in the course, but I wanted to',
'introduce them now as they are a very fundamental data type']
],
title='Additional Built-In Types'
),
InClassExampleFrame(
[
"We will keep working off of Python Basics.ipynb",
'Now I will go through the example material under "Exploring Data Types"'
],
title='Data Types Example',
block_title='Understanding the Different Data Types'
),
data_types_lab.presentation_frames(),
],
title='More about Data Types',
short_title='Data Types'
),
pl.Section(
[
lp.GraphicFrame(
images_path('class-object.pdf'),
title='Overview of Classes and Objects'
),
lp.DimRevealListFrame(
[
'In Python, everything is an object except for variable names, which are references to objects',
'Strings, floats, ints, lists, and tuples are types of objects. There are many more types of '
'objects and users can define their own types of objects',
'A class is a definition for a type of object. It defines how it is created, the data '
'stored in it, and the functions attached to it',
'We can write our own classes to create new types of objects to work with'
],
title='Everything is an Object. Every Object has a Class'
),
lp.DimRevealListFrame(
[
'From a single class definition, an unlimited number of objects can be created',
'Typically the class definition says it should accept some data to create the object',
'Then when you have multiple objects of the same type (created from the same class), '
'they will have the same functions (methods) attached to them, but different data stored within',
['For example, we can create two different lists. They will have different contents, but we can',
'do', pl.Monospace('.append'), 'on either of the lists']
],
title='Many Objects to One Class'
),
lp.MultiGraphicFrame(
[
images_path('class-object.pdf'),
images_path('list-object.pdf')
],
title='Lists are Objects',
vertical=False
),
lp.MultiGraphicFrame(
[
images_path('class-object.pdf'),
images_path('car-object.pdf')
],
title='We can Make Custom Objects Too',
vertical=False
),
lp.Frame(
[
pl.UnorderedList(['Constructing an object from a class looks like calling a function:']),
lp.Block(
[
pl.TextSize(-1),
use_class_example,
],
title='Using Custom Classes in Python'
),
],
title='Creating and Using Objects'
),
lp.DimRevealListFrame(
[
'I will not be teaching you about creating general classes in this course. It is very useful '
'but is generally more advanced. I encourage you to learn them outside the course.',
"We covered this material for two reasons:",
['To give a better understanding of how Python works in general, and why sometimes we '
'call functions as', pl.Monospace('something.my_func()'), 'rather than',
pl.Monospace('my_func()')],
['We are going to use', pl.Monospace('dataclasses'), 'to store our model data. They are',
'a simplified version of classes used mainly for storing data.']
],
title='Where we Will Focus in This Course'
),
lp.Frame(
[
pl.UnorderedList(['An organized way to store our model input data:']),
lp.Block(
[
pl.TextSize(-3),
dataclass_example,
],
title='Using Dataclasses in Python'
),
],
title='Dataclass Intro'
),
lp.DimRevealListFrame(
[
['A', pl.Monospace('dataclass'), 'is just a class which is more convenient to create, and',
'is typically used to group data together'],
['If you need to pass around multiple variables together, they make sense. For our models, we '
'will want to pass around all the inputs, so one', pl.Monospace('dataclass'), 'for all the',
'inputs to the model makes sense'],
'This way instead of having to pass around every input individually to every function, just '
'pass all the input data as one argument',
['Also enables easy tab-completion. What were the names of my inputs? Just hit tab after',
pl.Monospace('data.')]
],
title='What, When and Why Dataclasses?'
),
InClassExampleFrame(
[
"We will keep working off of Python Basics.ipynb",
'For this example, also go and download car_example.py and put it in the same folder',
'Now I will go through the example material under "Working with Classes"'
],
title='Classes Example',
block_title='Working with Classes and Creating Dataclasses'
),
classes_lab.presentation_frames(),
],
title='Classes and Dataclasses',
short_title='Classes'
),
pl.Section(
[
# TODO [#13]: add error handling to Jupyter example
lp.Frame(
[
pl.UnorderedList([
lp.DimAndRevealListItems([
"You have certainly already seen errors coming from your Python code. When they have come up, the code doesn't run.",
'Sometimes you actually expect to get an error, and want to handle it in some way, rather than having your program fail.'
],
vertical_fill=True)
]),
lp.Block(
try_except_example,
title='Example',
overlay=next_slide
)
],
title='Python Error Handling'
),
lp.DimRevealListFrame(
[
"Let's say you're receiving annuities. There is a single annuity which produces \$100 for 5 years. You receive this annuity in year 0 and in year 3.",
f"You might define the annuity cash flows as a list of 100, 5 times ({list_100_5})",
'Then you want to come up with your overall cash flows, going out to 15 years'
],
title='An Example where Error Handling is Useful'
),
lp.Frame(
[
lp.Block(
[
pl.TextSize(-1),
annuity_example,
],
title='Calculating the Sum of Unaligned Annuity Cash-Flows'
)
],
title='Applying Error Handling'
),
],
title='Error Handling',
short_title='Errors'
),
pl.PresentationAppendix(
[
lecture.pyexlatex_resources_frame,
*appendix_frames
]
)
]
def get_content():
random.seed(1000)
lecture = get_probability_lecture()
scenario_excel_lab = get_scenario_analysis_excel_lab_lecture().to_pyexlatex()
scenario_python_lab = get_scenario_analysis_python_lab_lecture().to_pyexlatex()
randomness_excel_lab = get_randomness_excel_lab_lecture().to_pyexlatex()
randomness_python_lab = get_randomness_python_lab_lecture().to_pyexlatex()
random_stock_lab = get_random_stock_model_lab_lecture().to_pyexlatex()
full_model_internal_randomness_lab = get_extend_model_internal_randomness_lab_lecture().to_pyexlatex()
appendix_frames = [
lecture.pyexlatex_resources_frame,
scenario_excel_lab.appendix_frames(),
scenario_python_lab.appendix_frames(),
randomness_excel_lab.appendix_frames(),
randomness_python_lab.appendix_frames(),
random_stock_lab.appendix_frames(),
full_model_internal_randomness_lab.appendix_frames(),
]
df_mono = pl.Monospace('DataFrame')
next_slide = lp.Overlay([lp.NextWithIncrement()])
with_previous = lp.Overlay([lp.NextWithoutIncrement()])
rand_mono = pl.Monospace('=RAND')
rand_between_mono = pl.Monospace('=RANDBETWEEN')
norm_inv_mono = pl.Monospace('=NORM.INV')
excel_random_normal_example = pl.Monospace('=NORM.INV(RAND(), 10, 1)')
random_module_mono = pl.Monospace('random')
py_rand_mono = pl.Monospace('random.random')
py_rand_uniform_mono = pl.Monospace('random.uniform')
py_rand_norm_mono = pl.Monospace('random.normalvariate')
py_random_link = Hyperlink('https://docs.python.org/3.7/library/random.html#real-valued-distributions',
'(and other distributions)')
py_random_normal_example = pl.Monospace('random.normalvariate(10, 1)')
random_seed_example = pl.Monospace('random.seed(0)')
next_slide = lp.Overlay([lp.NextWithIncrement()])
n_iter = pl.Equation(str_eq='n_{iter}')
df_mono = pl.Monospace('DataFrame')
df_std = pl.Monospace('df.std()')
df_mean = pl.Monospace('df.mean()')
random_choices_mono = pl.Monospace('random.choices')
random_choices_example = pl.Monospace("random.choices(['Recession', 'Normal', 'Expansion'], [0.3, 0.5, 0.2])")
return [
pl.Section(
[
lp.TwoColumnGraphicDimRevealFrame(
[
'So far everything in our models has been deterministic',
'Further, we have not explored any scenarios in our models, we have taken the base case as '
'the only case',
'Unfortunately, the real world is very random. Many possible scenarios could occur.'
],
[
images_path('dice.jpg'),
],
title='Why Model Probability'
),
lp.DimRevealListFrame(
[
'There are a few ways we can gain a richer understanding of the modeled situation by '
'incorporating probability',
f'The simplest is {pl.Bold("scenario modeling")}, in which different situations are defined with probabilities, '
'and the result of the model is the expected value across the cases.',
['Another is', pl.Bold('internal randomness'),
'where randomness is incorporated directly within '
'the model logic'],
['Finally,', pl.Bold("Monte Carlo simulation"),
'treats the model as deterministic but externally varies the '
'inputs to get a distribution of outputs.']
],
title='How to Bring Probability In'
),
],
title='Motivation for Probability Modeling',
short_title='Intro',
),
pl.Section(
[
lp.Frame(
[
pl.UnorderedList([
lp.DimAndRevealListItems([
['When something is measured numerically, it can be either a', pl.Bold('discrete'),
'variable, or a', pl.Bold('continuous'), 'variable.'],
])
]),
lp.Block(
[
pl.Equation(str_eq=r'x \in \{x_1, x_2, ... x_n\}', inline=False),
pl.VSpace(-0.4),
pl.UnorderedList([
[pl.Equation(str_eq=r'\{x_1, x_2, ... x_n\}:'), 'A specific set of values'],
])
],
title='Discrete Variables'
),
lp.Block(
[
pl.Equation(str_eq=r'x \in \mathbb{R} \text{ or } [a, b]', inline=False),
pl.VSpace(-0.4),
pl.UnorderedList([
[pl.Equation(str_eq='\mathbb{R}:'), 'All real numbers'],
[pl.Equation(str_eq='[a, b]:'), 'Some interval between two values or infinity'],
])
],
title='Continuous Variables'
),
],
title='Math Review: Discrete and Continuous Variables'
),
lp.Frame(
[
pl.TextSize(-3),
pl.UnorderedList([
lp.DimAndRevealListItems([
[pl.Bold('Expected value'), 'is the average outcome over repeated trials'],
"It is generally useful to get a single output from multiple possible cases",
], dim_earlier_items=False)
]),
lp.Block(
[
pl.Equation(str_eq=r'E[x] = \sum_{i=1}^{N} p_i x_i', inline=False),
pl.VSpace(-0.4),
pl.UnorderedList([
[pl.Equation(str_eq=r'E[x]:'), 'Expected value for', pl.Equation(str_eq='x')],
[pl.Equation(str_eq=r'x_i:'), 'A specific value for', pl.Equation(str_eq='x')],
[pl.Equation(str_eq=r'p_i:'), 'The probability associated with value',
pl.Equation(str_eq='x_i')],
[pl.Equation(str_eq=r'N:'), 'The total number of possible values of',
pl.Equation(str_eq='x')],
])
],
title='Discrete Variables'
),
lp.Block(
[
pl.Equation(str_eq=r'E[x] = \frac{1}{N} \sum_{i=1}^{N} x_i', inline=False),
pl.VSpace(-0.4),
pl.UnorderedList([
[pl.Equation(str_eq=r'N:'), 'The number of samples collected for',
pl.Equation(str_eq='x')],
])
],
title='Continuous Variables'
),
],
title='Math Review: Expected Value'
),
lp.GraphicFrame(
images_path('different-variance-plot.pdf'),
title='Math Review: Variance in One Picture'
),
lp.Frame(
[
pl.TextSize(-2),
pl.UnorderedList([
lp.DimAndRevealListItems([
[pl.Bold('Variance'), 'and', pl.Bold('standard deviation'),
'are measures of the dispersion '
'of values of a random variable.'],
'Variance is the real quantity of interest, but standard deviation is easier to understand '
'because it has the same units as the variable, while variance has units squared'
], dim_earlier_items=False),
]),
lp.Block(
[
EquationWithVariableDefinitions(
r'Var[x] = \sigma^2 = \frac{1}{N - 1} \sum_{i=1}^{N} (x_i - \mu)^2',
[
[pl.Equation(str_eq=r'N:'), 'Number of samples of', pl.Equation(str_eq=r'x')],
[pl.Equation(str_eq=r'\mu:'), 'Sample mean'],
]
),
],
title='Variance of a Continuous Variable'
),
lp.Block(
[
EquationWithVariableDefinitions(
r'\sigma = \sqrt{Var[x]}',
[
[pl.Equation(str_eq=r'\sigma:'), 'Standard deviation'],
],
space_adjustment=-0.5
),
],
title='Standard Deviation'
),
],
title='Math Review: Variance and Standard Deviation'
),
lp.TwoColumnGraphicDimRevealFrame(
[
['A', pl.Bold('probability distribution'),
'represents the probabilities of different values of '
'a variable'],
'For discrete variables, this is simply a mapping of possible values to probabilities, e.g. for a coin '
'toss, heads = 50% and tails = 50%',
'For continuous variables, a continuous distribution is needed, such as the normal distribution',
],
graphics=[
images_path('normal-distribution.png'),
],
title='Math Review: Probability Distributions'
),
lp.TwoColumnGraphicDimRevealFrame(
[
pl.TextSize(-2),
["You've probably heard of the", pl.Bold('normal distribution'),
'as it is very commonly used because it occurs a lot in nature'],
['It is so common because of the', pl.Bold('central limit theorem'), 'which says that '
'averages of variables will follow a normal distribution, regardless of the distribution of the '
'variable itself'],
'This has many applications. For example, we can view the investment rate as an average across '
'individual investment returns, and so it will be normally distributed.',
],
graphics=[
images_path('normal-distribution-percentages.png'),
],
title='Math Review: Normal Distribution'
),
lp.Frame(
[
pl.TextSize(-3),
pl.UnorderedList([
lp.DimAndRevealListItems([
'We want to extend our retirement model to say that the investment return is not constant.',
'We can treat the interest rate as either a discrete (specific values) or a continuous '
'(range of values, more realistic) variable'
], dim_earlier_items=False),
]),
lp.Block(
[
pl.Center(
[
lt.Tabular(
[
lt.ValuesTable.from_list_of_lists([
['Interest Rate', 'Probability']
]),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['2%', '30%'],
['5%', '50%'],
['7%', '20%'],
]),
],
align='cc'
)
]
)
],
title='As a Discrete Variable'
),
lp.Block(
[
pl.Equation(str_eq=r'r_i \sim N(\mu, \sigma^2)', inline=False),
pl.VSpace(-0.5),
pl.UnorderedList([
[pl.Equation(str_eq=r'N:'), 'Normal distribution'],
[pl.Equation(str_eq=r'\mu:'), 'Interest rate mean'],
[pl.Equation(str_eq=r'\sigma:'), 'Interest rate standard deviation'],
])
],
title='As a Continuous Variable'
),
],
title='A Non-Constant Interest Rate'
),
],
title='Mathematical Tools for Probability Modeling',
short_title='Math Review'
),
pl.Section(
[
lp.Frame(
[
pl.TextSize(-1),
lp.Block(
[
pl.Center(
[
lt.Tabular(
[
lt.ValuesTable.from_list_of_lists([
['State of Economy', 'Interest Rate', 'Savings Rate', 'Probability']
]),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Recession', '2%', '35%', '30%'],
['Normal', '5%', '30%', '50%'],
['Expansion', '7%', '25%', '20%'],
]),
],
align='l|ccc'
)
]
)
],
title='Interest Rate Scenarios'
),
pl.UnorderedList([
lp.DimAndRevealListItems([
['In scenario modeling, different cases for model parameters are chosen. Several '
'parameters may be altered at once in a given case.'],
"Here we are making the different cases the state of the economy. When the economy is doing "
"poorly, the individual earns a lower return, but also saves more because they don't want to "
"overspend at a bad time",
"When the economy does well, the individual earns a higher return, but also spends more"
])
]),
],
title='Scenario Modeling'
),
lp.DimRevealListFrame(
[
['We can implement scenario modeling', pl.Bold('internal'), 'or', pl.Bold('external'),
'to our model'],
['With an internal implementation, the cases are built', pl.Underline('into the model logic'),
'itself, '
'and model logic also takes the expected value of the case outputs. The inputs of the model',
'are now the cases and probabilities.'],
['With an external implementation, the', pl.Underline('model logic is left unchanged,'),
'instead the '
'model is run separately with each case, then the expected value is calculated across the outputs '
'from the multiple model runs.'],
],
title='Implementing Scenario Modeling'
),
lp.Frame(
[
pl.Center(
[
lt.Tabular(
[
lt.ValuesTable.from_list_of_lists([
[pl.Bold('Internal'), pl.Bold('External')]
]),
lt.MidRule(),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Original model is now an old version',
'Original model can still be used normally'],
]),
# TODO [#14]: each row should come one per slide, but need to allow overlays in lt items
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Model runs exactly as before',
'Getting full results of model requires running the model multiple times and '
'aggregating output']
]),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Model complexity has increased', 'Model complexity unchanged']
]),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Complexity to run model is unchanged',
'Complexity to run model has increased']
]),
],
align='L{5cm}|R{5cm}'
)
]
)
],
title='Internal or External Scenario Analysis?'
),
lp.DimRevealListFrame(
[
'For internal scenario analysis, set up a table of the cases and probabilities. Then calculate the '
'expected value of these cases for each model parameter. Then use the expected value as the new '
'model parameter.',
'For external scenario analysis, a data table is useful. Create the data table of outputs for each case '
'and another table of case probabilities, then combine them to produce the expected value of '
'the output.',
'If you are trying to change more than two inputs at once in external scenario '
'analysis, this becomes more '
'challenging but you can assign a number to each set of inputs and have the model look up the '
'inputs based on the case number, using the case number as the data table input.'
],
title='Scenario Analysis in Excel'
),
InClassExampleFrame(
[
'I will now go through adding external scenario analysis to the Dynamic Salary Retirement Model '
'in Excel',
'The completed exercise on the course site as "Dynamic Salary Retirement Model Sensitivity.xlsx"',
],
title='Scenario Analysis in Excel',
block_title='Adding Scenario Analysis to the Dynamic Retirement Excel Model'
),
scenario_excel_lab.presentation_frames(),
lp.DimRevealListFrame(
[
['For internal scenario analysis, set up a', df_mono,
'or dictionary of the cases and probabilities. Then calculate '
'the expected value of these cases for each model parameter. Then use the expected value as the new '
'model parameter.'],
'For external scenario analysis, just call your model function with each input case, collect the '
'results, and combine them to produce the expected value of the output.'
],
title='Scenario Analysis in Python'
),
InClassExampleFrame(
[
'I will now go through adding external scenario analysis to the Dynamic Salary Retirement Model '
'in Python',
'he completed exercise on the course site as "Dynamic Salary Retirement Model Scenario.ipynb"',
],
title='Scenario Analysis in Python',
block_title='Adding Scenario Analysis to the Dynamic Retirement Python Model'
),
scenario_python_lab.presentation_frames(),
],
title='Scenario Modeling'
),
pl.Section(
[
lp.DimRevealListFrame(
[
["Using the technique of", pl.Bold('internal randomness,'),
'something random is added internally to the model'],
'Instead of taking a fixed input, random values for that variable are drawn',
'This technique can be used with both discrete and continuous variables'
],
title='What is Internal Randomness?'
),
lp.GraphicFrame(
internal_randomness_graphic(),
title='Internal Randomness in One Picture'
),
lp.DimRevealListFrame(
[
'Internal randomness makes sense when the random behavior is integral to your model',
'If you are just trying to see how changing inputs affects outputs, or trying to get confidence intervals for outputs, '
'an external method such as sensitivity analysis or Monte Carlo simulation would make more sense.',
'For example, if we want to allow investment returns to vary in our retirement model, an external method fits well because '
'the core model itself is deterministic',
'If instead we were modeling a portfolio, we might use internal randomness to get the returns for each asset.'
],
title='Should I Use Internal Randomness?'
),
lp.DimRevealListFrame(
[
'Similarly to our discussion of internal vs. external sensitivity analysis, internal randomness keeps '
'operational complexity (how to run the model) low, but increases model complexity.',
'The main drawback of internal randomness is that the same set of inputs will give different outputs each time the model is run',
'While this is the desired behavior, it can make it difficult to determine whether everything is working.'
],
title='Internal Randomness Advantages and Pitfalls'
),
lp.TwoColumnGraphicDimRevealFrame(
[
'Instead of taking the input as fixed, draw it from a distribution',
'We need to define a distribution for each input we want to randomize. This will typically be a normal distribution, and then '
'we just need to give it a reasonable mean and standard deviation',
'Put the most reasonable or usual value as the mean. Then think about the probabilities of the normal distribution relative '
'to standard deviation to set it'
],
graphics=[
images_path('normal-distribution-percentages.png'),
],
title='Internal Randomness with Continuous Variables'
),
lp.DimRevealListFrame(
[
['The main functions for randomness in Excel are', rand_mono, 'and', rand_between_mono],
'The latter gives a random number between two numbers, while the former gives a random number '
'between 0 and 1. Both of these draw from a uniform distribution (every number equally likely)',
['Meanwhile, the', norm_inv_mono,
'function gives the value for a certain normal distribution at a certain probability (it is not random)'],
'We can combine these two functions to draw random numbers from a normal distribution',
[excel_random_normal_example,
'would draw a number from a normal distribution with mean 10 and standard deviation 1'],
],
title='Internal Randomness with Continuous Variables in Excel'
),
InClassExampleFrame(
[
'I will now go through generating random continuous variables '
'in Excel',
'The completed exercise on the course site is called "Generating Random Numbers.xlsx"',
'We will focus only on the "Continuous" sheet for now',
],
title='Example for Continuous Random Variables in Excel',
block_title='Generating Random Numbers from Normal Distributions in Excel'
),
randomness_excel_lab.presentation_frames(),
lp.DimRevealListFrame(
[
['In Python, we have the built-in', random_module_mono, 'module'],
['It has functions analagous to those in Excel:', py_rand_mono, 'works like', rand_mono,
'and', py_rand_uniform_mono, 'works like', rand_between_mono],
['Drawing numbers from a normal distribution', py_random_link, 'is easier: just one function',
py_rand_norm_mono],
[py_random_normal_example,
'would draw a number from a normal distribution with mean 10 and standard deviation 1']
],
title='Internal Randomness with Continuous Variables in Python'
),
InClassExampleFrame(
[
'I will now go through generating random continuous variables '
'in Python',
'The completed exercise on the course site is called "Generating Random Numbers.ipynb"',
'We will focus only on the "Continuous" section for now',
],
title='Example for Continuous Random Variables in Python',
block_title='Generating Random Numbers from Normal Distributions in Python'
),
randomness_python_lab.presentation_frames(),
lp.DimRevealListFrame(
[
'We can also build randomness into the model for discrete variables',
"With discrete variables, our distribution is just a table of probabilities for the different values",
'To pick a random value for a discrete variable, first add another column to your table which has the '
'cumulative sum of the prior probabilties, and then another column which is that column plus the '
'current probability',
'Then generate a random number between 0 and 1 from a uniform distribution',
'If the generated number is between the probability and the cumulative sum of prior probabilities, choose that case'
],
title='Internal Randomness with Discrete Variables'
),
lp.Frame(
[
pl.TextSize(-1),
lp.Block(
[
pl.Center(
[
lt.Tabular(
[
lt.ValuesTable.from_list_of_lists([
['State of Economy', 'Interest Rate', 'Probability', 'Begin Range',
'End Range']
]),
lt.MidRule(),
lt.ValuesTable.from_list_of_lists([
['Recession', '2%', '30%', '0%', '30%'],
['Normal', '5%', '50%', '30%', '80%'],
['Expansion', '7%', '20%', '80%', '100%'],
]),
],
align='L{2cm}|cccc'
)
]
)
],
title='Interest Rate Scenarios'
),
pl.UnorderedList([
pl.TextSize(-2),
lp.DimAndRevealListItems([
'The Begin Range column is calculated as the cumulative sum of prior probabilities',
'The End Range column is calculated as Begin Range + Probability',
"Generate a random number between 0 and 1. If it is between the begin and end range, "
"that is the selected value",
"If it's 0.15, it's a recession. If it's 0.45, it's a normal period. If it's 0.94, it's "
"an expansion period."
], vertical_fill=True)
]),
],
title='An Example of Internal Randomness with Discrete Variables'
),
lp.DimRevealListFrame(
[
'The steps in the preceeding slides need to be carried out manually in Excel',
['In Python, there is a built-in function which is doing all of this in the background,',
random_choices_mono],
['Simply do', random_choices_example, 'to yield the exact same result for the prior example']
],
title='Random Discrete Variables in Python'
),
InClassExampleFrame(
[
'I will now go through generating random discrete variables '
'in both Excel and Python',
'We will be continuing with the same Excel workbook and Jupyter notebook from before, '
'"Generating Random Numbers.xlsx" and "Generating Random Numbers.ipynb"',
'We will focus only on the "Discrete" sheet/section now',
],
title='Example for Discrete Random Variables in Excel and Python',
block_title='Generating Random Numbers from Discrete Distributions in Excel and Python'
),
random_stock_lab.presentation_frames(),
InClassExampleFrame(
[
'I will now add internal randomness with discrete variables to '
'both the Excel and Python Dynamic Salary Retirement models to simulate economic conditions '
'changing year by year',
'The completed models on the course site are called '
'"Dynamic Salary Retirement Model Internal Randomness.xlsx" and '
'"Dynamic Salary Retirement Model Internal Randomness.ipynb"',
],
title='Adding Internal Randomness to Excel and Python Models',
block_title='Extending the Dynamic Salary Retirement Model with Internal Randomness'
),
full_model_internal_randomness_lab.presentation_frames(),
],
title='Internal Randomness'
),
pl.PresentationAppendix(appendix_frames),
]
def get_content():
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
df_mono = pl.Monospace('DataFrame')
next_slide = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
df_to_excel_example = pl.Python(
"df.to_excel('data.xlsx', sheet_name='My Data', index=False)")
df_from_excel_example = pl.Python(
"df = pd.read_excel('data.xlsx', sheet_name='My Data')")
index_false_mono = pl.Monospace('index=False')
addin_install_mono = pl.Monospace('xlwings addin install')
addin_install_success = pl.Monospace(
'Successfuly installed the xlwings add-in! Please restart Excel.')
random_seed_py = pl.Monospace('random_seed.py')
random_seed_excel = pl.Monospace('random_seed.xlsm')
quickstart_mono = pl.Monospace('xlwings quickstart')
quickstart_project_mono = pl.Monospace(
'xlwings quickstart my_project_name')
cd_mono = pl.Monospace('cd')
xw_func_decorator = pl.Monospace('@xw.func')
xw_arg_decorator = pl.Monospace('@xw.arg')
xw_ret_decorator = pl.Monospace('@xw.ret')
x_mono = pl.Monospace('x')
expand_table_mono = pl.Monospace("expand='table'")
random_choice_mono = pl.Monospace('random_choice')
random_choice_py = pl.Monospace('random.choices')
lecture = get_combining_excel_python_lecture()
pd_read_write_exercise = get_read_write_excel_pandas_lab_lecture(
).to_pyexlatex()
xlwings_exercise = get_read_write_xlwings_lab_lecture().to_pyexlatex()
read_from_excel_example = pl.Python("""
my_value = sht.range("G11").value # single value
# all values in cell range
my_value = sht.range("G11:F13").value
# expands cell range down and right getting all values
my_values = sht.range("G11").expand().value
""")
write_to_excel_example = pl.Python("""
sht.range("G11").value = 10
sht.range("G11").value = [10, 11] # horizontal
sht.range("G11:G12").value = [10, 11] # vertical
# table, DataFrame from elsewhere
sht.range("G11").value = df
""")
return [
pl.Section([
lp.DimRevealListFrame([
"We have learned how to use both Excel and Python to solve problems. Throughout this process, there "
"were advantages and disadvantages of each tool for each problem.",
"I wanted you to know both tools so you could pick whichever is best to tackle your problem",
"For larger problems, you'll likely find some parts are better with Excel and some with Python",
"After this lecture, you won't need to choose one anymore, you can use both at once."
],
title='Leveraging the Power of Both Tools'),
],
title='Introduction'),
pl.Section([
lp.DimRevealListFrame([
[pd_mono, 'has built-in tools for working with Excel'],
[
pd_mono, 'can read Excel workbooks into', dfs_mono,
'and it can write', dfs_mono, 'back to Excel workbooks'
],
'For simple uses, this may be enough. If you just need to get data from somewhere once and put it in your '
'workbook, or you have your data in Excel and want to analyze it in Python, this is sufficient',
[
"If you want to manipulate your workbook from Python, or you want to run Python code from your "
"workbook, look to", xlwings_mono
]
],
title=f'How Far does {pd_mono} Get Us?'),
lp.Frame([
lp.Block([
df_from_excel_example,
pl.VSpace(-0.3),
pl.UnorderedList([
"If you don't pass a sheet name, it will take the first sheet."
])
],
title='Reading Excel Files',
overlay=next_slide),
pl.VFill(),
lp.Block(
[
df_to_excel_example,
pl.VSpace(-0.3),
pl.UnorderedList(
[[
'We are passing', index_false_mono,
'because usually the 0, 1, 2 ... index is not useful'
],
[
"If you had set your index to something useful, then don't include",
index_false_mono
]])
],
title='Writing to Excel Files',
overlay=next_slide),
pl.VFill(),
lp.AlertBlock([[
'When', pd_mono,
'writes to a workbook, it replaces the file. Do not write over an existing '
'workbook that you want to keep!'
]],
title='Careful When Writing!',
overlay=next_slide),
],
title=f'Reading and Writing to Excel Files with {pd_mono}'
),
lp.Frame([
InClassExampleBlock([
pl.UnorderedList([
'Download the contents of the "Read Write Excel Pandas" folder in Examples',
'Ensure that you put the Excel file and notebook in the same folder for it to work',
'Follow along with the notebook'
])
],
title=
f'Read and Write to Excel using {pd_mono}')
],
title='Showcasing Reading and Writing to Excel Files'),
pd_read_write_exercise.presentation_frames(),
],
title=f'To and From Excel with {pd_mono}',
short_title=pd_mono),
pl.Section([
lp.TwoColumnGraphicDimRevealFrame([[
'The easiest way to use Python from in Excel, or Excel from in Python, is',
xlwings_mono
], "In Windows, it's based off the Microsoft COM API, which is some common tools they give for creating "
"plugins.",
"It's still in active development, but overall it works pretty well and is far beyond where we were "
"a few years ago"],
graphics=[
images_path(
'xlwings-logo.png')
],
title=
f'Introducing {xlwings_mono}'),
lp.DimRevealListFrame(
[['There are two main ways to use', xlwings_mono],
[
'You can',
pl.
Bold('manipulate Excel from Python,'),
'which gives you the full power of Excel from',
"within Python. In this class we'll focus on reading and writing values, but you can do anything",
"that you would normally be able to in Excel, but by executing Python code."
],
[
'Or you can',
pl.Bold('run Python from Excel'),
'using one of two approaches:',
pl.Underline('Python as a VBA replacement'), 'and',
pl.Underline('user-defined functions (UDFs)')
],
'We will focus on manipulating Excel from Python in this class. I encourage you to explore '
'the other two approaches on your own.'],
title=f'What are the Main Ways to use {xlwings_mono}?'),
lp.TwoColumnGraphicDimRevealFrame([
pl.TextSize(-1),
[
xlwings_mono,
'allows us to write Python values into Excel and fetch Excel values into Python'
],
'There is also a complete VBA API, meaning you can do everything that you could do with VBA '
'from within Python, which means you have the full capabilities of Excel within Python',
'There are also convenient features to work with entire tables at once rather than '
'a single value'
],
graphics=[
images_path(
'python-excel-logo.png')
],
title=
'Using Python to Drive Excel Models'
),
lp.Frame([
lp.Block([read_from_excel_example],
title='Read Values from Excel'),
lp.Block([write_to_excel_example],
title='Write Values to Excel')
],
title='Write and Read Values to and from Excel'),
InClassExampleFrame([
'Download the contents of the "xlwings" folder in Examples',
'Ensure that you put the Excel file and notebook in the same folder for it to work',
'Follow along with the notebook'
],
title=f'How to Use {xlwings_mono}',
block_title=f'Trying out {xlwings_mono}'),
xlwings_exercise.presentation_frames(),
],
title=
f'Introducing Full Python-Excel Connection with {xlwings_mono}',
short_title=f'{xlwings_mono}'),
pl.PresentationAppendix([
lecture.pyexlatex_resources_frame,
pd_read_write_exercise.appendix_frames(),
xlwings_exercise.appendix_frames(),
])
]
def get_lab_exercise() -> LabExercise:
pd_mono = pl.Monospace('pandas')
dfs_mono = pl.Monospace('DataFrames')
next_slide = lp.Overlay([lp.UntilEnd(lp.NextWithIncrement())])
df_to_excel_example = pl.Python(
"df.to_excel('data.xlsx', sheet_name='My Data', index=False)")
df_from_excel_example = pl.Python(
"df = pd.read_excel('data.xlsx', sheet_name='My Data')")
index_false_mono = pl.Monospace('index=False')
addin_install_mono = pl.Monospace('xlwings addin install')
addin_install_success = pl.Monospace(
'Successfuly installed the xlwings add-in! Please restart Excel.')
random_seed_py = pl.Monospace('random_seed.py')
random_seed_excel = pl.Monospace('random_seed.xlsm')
quickstart_mono = pl.Monospace('xlwings quickstart')
quickstart_project_mono = pl.Monospace(
'xlwings quickstart my_project_name')
cd_mono = pl.Monospace('cd')
xw_func_decorator = pl.Monospace('@xw.func')
random_choice_mono = pl.Monospace('random_choice')
random_choices_mono = pl.Monospace('random_choices')
random_choice_py = pl.Monospace('random.choices')
xlwings_mono = pl.Monospace('xlwings')
bullet_contents = [
[[
'If you have not already created your', xlwings_mono,
'project, go back two slides', 'and follow those steps.'
],
[
'Edit the .py file to add a function', random_choice_mono,
'which will call', random_choice_py
],
'The function should accept the items to choose from, and the probabilities.',
'Write out a few possible items to choose from in your workbook. Put probabilities next '
'to them.',
[
'Call your', random_choice_mono,
'function on these inputs, and see it pick a random item'
]],
[[
'Work off the existing', xlwings_mono,
'project from the Level 1 exercise'
],
[
'Now add an additional function', random_choices_mono,
'which will accept the items to',
'choose from, the probabilities, and the number of random choices to generate.'
],
[
'The function should return multiple random choices. In Excel, you should see multiple cells of output,'
'with each cell containing a random choice.'
],
[
'If you return a list from the Python function, it should create this behavior.'
]]
]
return LabExercise(bullet_contents,
'Write a Simple UDF',
f"Getting your Feet Wet with {xlwings_mono}",
label='lab:intro-udf')
def get_content():
random.seed(1000)
lecture = get_visualization_lecture()
intro_pandas_lab = get_intro_to_pandas_lab_lecture().to_pyexlatex()
styling_pandas_lab = get_pandas_styling_lab_lecture().to_pyexlatex()
graphing_lab = get_intro_python_visualization_lab_lecture().to_pyexlatex()
appendix_frames = [
lecture.pyexlatex_resources_frame,
intro_pandas_lab.appendix_frames(),
styling_pandas_lab.appendix_frames(),
graphing_lab.appendix_frames()
]
ret_model = RetirementModel()
ret_df = ret_model.get_formatted_df(num_years=12)
ret_table = lt.Tabular.from_df(ret_df, extra_header=pl.Bold('Retirement Info'))
plt_mono = pl.Monospace('matplotlib')
df_mono = pl.Monospace('DataFrame')
df_basic_example = pl.Python(
"""
>>> import pandas as pd
>>> df = pd.DataFrame()
>>> df['Sales'] = [1052, 212, 346]
>>> df['Category'] = ['Aprons', 'Apples', 'Bowties']
df
"""
)
plot_example_code = pl.Python(
"""
>>> %matplotlib inline
>>> ret_df.plot.line(x='Time', y='Salaries')
"""
)
return [
pl.Section(
[
lp.DimRevealListFrame(
[
"So far we've had one main output from our model, number of years",
"Salaries and wealth over time have also been outputs, but we haven't had a good way of understanding "
"that output. It's a bunch of numbers.",
"This is where visualization comes in. We have some complex result, and want to make it easily "
"interpretable."
],
title='Why Visualize?'
),
lp.Frame(
[
pl.Center(ret_table)
],
title='What we Have so Far'
),
lp.GraphicFrame(
images_path('excel-insert-chart.png'),
title='Visualization in Excel'
),
lp.GraphicFrame(
lg.ModifiedPicture(
images_path('python-visualization-landscape.jpg'),
[
lg.Path('draw', [(0.52, 0.52), (0.85, 0.67)], options=['red'], draw_type='rectangle',
overlay=lp.Overlay([2]))
]
),
title='An Overwhelming Number of Options in Python'
),
lp.DimRevealListFrame(
[
["Ultimately, we will be creating graphs using", plt_mono, "but we won't use it directly."],
["Instead, we will use", pd_mono],
[pd_mono, "is actually creating its graphs using", plt_mono,
"for us, but it is simpler to use."]
],
title='Explaining Python Visualization in This Class'
),
InClassExampleFrame(
[
'I will now go back to the "Dynamic Salary Retirement Model.xlsx" Excel model to '
'add visualization',
'I have also uploaded the completed workbook from this exercise '
'as "Dynamic Salary Retirement Model Visualized.xlsx"',
'Follow along as I go through the example.',
],
title='Visualization in Excel',
block_title='Adding Graphs to the Dynamic Salary Retirement Excel Model'
),
],
title='Visualization Introduction',
short_title='Intro'
),
pl.Section(
[
lp.DimRevealListFrame(
[
[pd_mono, "does", pl.Bold('a lot'), 'more than just graphing. We will use it throughout the '
'rest of the class.'],
"Previously we've worked with lists, numbers, strings, and even our custom types (our model dataclasses)",
[pd_mono, "provides the", df_mono, "as a new type that we can use."],
f'Before we can get to graphing, we must learn how to use the {df_mono}.'
],
title='Some Setup Before we can Visualize in Python'
),
lp.Frame(
[
['A', df_mono, 'is essentially a table. It has rows and columns, just like in Excel.'],
pl.VFill(),
lp.Block(
[
pl.UnorderedList([
'Add or remove columns or rows',
'Group by and aggregate',
'Load in and output data from/to Excel and many other formats',
'Merge and join data sets',
'Reshape and pivot data',
'Time-series functionality',
'Slice and query your data',
'Handle duplicates and missing data'
])
],
title=f'Some Features of the {df_mono}'
)
],
title=f'What is a {df_mono}?'
),
lp.Frame(
[
df_basic_example,
pl.Graphic(images_path('df-basic-example.png'), width=0.3)
],
title=f'A Basic {df_mono} Example'
),
InClassExampleFrame(
[
'I will now go through the notebook in '
'"Intro to Pandas and Table Visualization.ipynb"',
'Follow along as I go through the example.',
'We will complete everything up until DataFrame Styling'
],
title='Introduction to Pandas',
block_title='Creating and Using Pandas DataFrames'
),
intro_pandas_lab.presentation_frames(),
lp.DimRevealListFrame(
[
['It is possible to add styling to our displayed tabular data by styling the', df_mono],
'The styling is very flexible and essentially allows you to do anything',
'Out of the box, it is easy to change colors, size, and positioning of text, add a caption, do '
'conditional formatting, and draw a bar graph over the cells.'
],
title='Styling Pandas DataFrames'
),
InClassExampleFrame(
[
'I will now go through the next section in '
'"Intro to Pandas and Table Visualization.ipynb"',
'Follow along as I go through the example.',
'This time we are covering the remainder of the notebook starting from "DataFrame Styling"'
],
title='Introduction to Pandas',
block_title='Creating and Using Pandas DataFrames'
),
styling_pandas_lab.presentation_frames(),
],
title='Tables with Pandas DataFrames',
short_title='Pandas'
),
pl.Section(
[
lp.Frame(
[
lp.Block(
[
plot_example_code,
pl.Graphic(images_path('python-salaries-line-graph.pdf'), width=0.5)
],
title=f'Line Graphs using {pd_mono}'
)
],
title='A Minimal Plotting Example'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-line-graph.png'),
images_path('python-salaries-line-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Line Graphs'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-bar-graph.png'),
images_path('python-salaries-bar-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Bar Graphs'
),
lp.MultiGraphicFrame(
[
images_path('excel-salaries-box-whisker-plot.png'),
images_path('python-salaries-box-graph.pdf'),
],
vertical=False,
title='Basic Graph Types: Box and Whisker Plots'
),
InClassExampleFrame(
[
'I will now go through '
'"Intro to Graphics.ipynb"',
'Follow along as I go through the entire example notebook.',
],
title='Introduction to Graphing',
block_title='Graphing Using Pandas'
),
graphing_lab.presentation_frames(),
],
title='Graphing using Pandas',
short_title='Graphs'
),
pl.PresentationAppendix(appendix_frames)
]