00:03: hey everyone
00:04: nick dearmardis here teaching you
00:06: financial modeling today we're going to
00:08: be talking about
00:09: combining excel in python using pandas
00:13: and this is part of our lecture series
00:15: on combining excel and python
00:17: so we said that there are two main ways
00:20: that we can
00:22: combine excel in python that we're going
00:24: to explore in this course that's using
00:26: pandas and using excel wings
00:28: so we'll come back in the following
00:30: video to explore excel wings
00:32: but in this video we're going to look at
00:35: the pandas
00:36: way of going between excel and python
00:41: so this is generally a simpler
00:44: integration which
00:47: is not does not have nearly as many
00:50: features
00:52: but it's very fast and has a very simple
00:57: api that we can work with
01:00: so pandas we get data frames we can do
01:04: all this
01:04: visualization and different analysis and
01:07: it also
01:08: can go in and out of different data
01:10: formats so excel
01:12: files are one of those formats
01:16: so we can take a data frame and we can
01:20: output that to an excel workbook and
01:23: we can take an excel workbook and we can
01:26: read that in
01:27: to append this data frame so that's
01:30: pretty much the extent of the
01:32: integration between the two
01:35: and for a lot of cases this is all that
01:37: you need
01:38: if you have a python model and you just
01:41: need to get some data which is stored in
01:43: excel spreadsheet
01:45: then this is perfect um or
01:48: if uh you have an excel model
01:52: and you use python to collect the data
01:56: maybe through
01:57: web scraping or pulling from a database
01:59: or apis or
02:01: something which is going to be easier to
02:02: approach in python
02:05: you just want to take that data from
02:06: python and be able to load it into your
02:09: excel model
02:09: as a full worksheet this is also
02:12: a good way to go about it but for any
02:16: use case which is going to require a
02:18: tighter integration things going back
02:20: and forth between
02:22: excel and python or taking individual
02:25: values rather than an entire
02:27: worksheet or workbook at once then excel
02:31: wings
02:31: should be the way to go
02:35: but um oftentimes
02:38: we either have a python or excel model
02:41: and it's
02:42: easy just to transfer the data between
02:44: using pandas
02:47: so what does this actually look like
02:50: we'll look at a quick example here so
02:53: the first block here
02:54: is for reading excel files so
02:59: for reading excel files uh we're going
03:02: to use a method of panis library read
03:05: excel
03:06: so assume you've already imported pandas
03:08: as pd
03:09: then you can do pd.read excel and you
03:12: give it the name
03:13: or file path of your spreadsheet
03:16: if it's in the same folder you can just
03:18: give the name and if it's somewhere else
03:20: on your computer
03:21: then you would want to put the full file
03:23: path here
03:26: and this sheet name is optional you can
03:29: do that to say which
03:30: specific sheet you want to get out of
03:32: the workbook
03:34: and if you don't pass it then it's just
03:35: going to take the first
03:37: sheet out of the workbook
03:40: and it's always going to read in the
03:42: entire worksheet
03:44: so if you have multiple different tables
03:46: in the worksheet
03:47: that can be another reason to go to
03:50: using excel wings you can more flexibly
03:52: grab
03:54: individual things out of a worksheet
03:56: whereas pandas is going to read the
03:58: entire worksheet as a data frame so
04:00: if you have anything other than just one
04:02: table in that worksheet
04:05: then pandas is going to be picking up a
04:09: lot of blank cells and things like that
04:11: and you'll have to do additional cleanup
04:13: so it's definitely easiest if you just
04:16: have
04:16: one tab one table on the worksheet
04:21: and then for writing in that case we
04:24: would already have a data frame created
04:27: and then we can use the method of that
04:29: data frame
04:30: to excel to output to an excel workbook
04:34: um and so there similarly to reading you
04:38: give
04:38: the name or file path of the
04:42: output so if you just put a name it's
04:44: going to go in the same folder
04:46: if you put a full file path then you can
04:47: go wherever you want
04:50: and you want to give the
04:54: name of the sheet that we're going to
04:55: output to
04:57: and the last thing we're including here
04:59: is index equals false
05:00: so index in penis is that
05:03: thing which goes on the left-hand side
05:06: of
05:07: the data frame which by default is just
05:10: going to be 0 1
05:11: 2 like auto incrementing um
05:15: so if you do just have that 0 1 2 and
05:18: you don't care about it
05:19: pass this index equals false if you have
05:22: actually set the index to something so
05:24: the index has meaningful values in it
05:27: then you don't want to put that index
05:29: equals false
05:30: assuming you want to have that in the
05:32: output as well
05:36: and i want to
05:39: definitely highlight this warning this
05:42: is very very important
05:45: when you write to an excel workbook
05:48: using pandas it's going to replace
05:52: whatever is there and it's going to
05:55: whatever was there before is going to be
05:57: gone
05:58: and there is no undo this is true
06:02: in general with programming if you tell
06:05: the computer
06:06: to do something it's going to do it it's
06:08: not going to ask you
06:09: are you sure you want to do this uh it's
06:12: only if you're using an app which has
06:14: built in those kind of protections
06:16: um but with a programming language it's
06:18: going to do whatever you tell it to
06:20: and so if you tell it to output to a
06:22: workbook
06:23: which already has your full excel model
06:26: in it
06:28: you really meant to just output to one
06:29: sheet of that workbook
06:31: but you accidentally output it to the
06:33: entire workbook it's just going to erase
06:35: everything that you had in your model
06:36: it's going to be gone forever
06:39: so be really careful about this make
06:42: sure to back up your work
06:45: and generally uh be really careful
06:48: if you're going to try and write a sheet
06:51: into an existing excel
06:52: model um it's better generally to just
06:56: write to a separate workbook
06:58: because then uh that workbook is always
07:00: just being generated by the code
07:02: and you don't really care about what's
07:05: in there it's just getting
07:06: replaced whenever you run the code
07:10: so just be careful about this
07:13: you cannot undo it once you have written
07:16: it over so you get a very bad day
07:18: if you made a mistake with this so
07:20: that's why i always
07:22: among other reasons always recommend
07:23: that people back up their work
07:25: regularly uh you know you can use a
07:28: service like like dropbox or something
07:31: to do it automatically or you can
07:33: you know have your own manual system of
07:34: copying files
07:36: whatever you do just be careful when you
07:38: write
07:39: to excel workbooks and this you know
07:43: this is the only
07:44: uh part of the course where we've
07:47: actually outputted a file um
07:50: this is really a general caution about
07:52: any time you're going to output a file
07:55: in any programming language it's going
07:57: to overwrite whatever is there
07:59: so just be careful about this
08:05: so let's go look at a quick example of
08:09: how we can work with pandas to read and
08:12: write
08:12: excel files
08:16: so we'll hop over to the jupyter
08:18: notebook here read write
08:20: excel pandas and we can see it works
08:23: with
08:24: a workbook called stock data so i'm just
08:27: going to quickly
08:29: open up that stock data excel workbook
08:32: so that we can see what the contents are
08:36: and you can see it's got two worksheets
08:38: here the first sheet and the second
08:39: sheet
08:40: first sheet got microsoft and apple
08:42: stock prices
08:43: over time second sheet amazon and
08:46: facebook
08:47: stock prices over time
08:51: so of course first we want to import
08:54: pandas
08:55: and then we can read that data
08:58: into a data frame so we read the stock
09:02: data file we didn't tell it any sheet or
09:03: anything
09:04: and we can see that it loaded the first
09:06: sheet by default which has microsoft
09:09: and apple and now we have all that and a
09:11: data frame
09:14: um but if we want that second sheet then
09:17: we just pass receipt name
09:18: here the second sheet whatever the name
09:21: of the sheet
09:22: and now we get the amazon and facebook
09:25: worksheet
09:29: so now coming to outputting
09:32: the data frame to an excel file so let's
09:35: look at
09:36: let's do something with the data frame
09:37: so that we know it's different uh
09:39: here we're just adding a column which
09:42: has the string stuff
09:44: in it a custom column
09:47: and now we're going to go ahead and
09:49: output this
09:50: so i'm going to run this to excel
09:53: creating newbook.xlsx
09:56: and let's take a look at what we got in
10:00: the new book
10:03: so we can see um
10:08: that we have the data which uh
10:11: was in the data frame has now come over
10:14: to the excel worksheet
10:16: the stock uh tickers prices and dates
10:20: um and we also have this additional
10:23: column that we added
10:26: you'll also notice um that it just put
10:30: it on a sheet one
10:31: that's going to be the default sheet
10:33: name
10:35: and you can see that it brought over
10:36: this index 0 1 2
10:38: 3 4 5 6 7 as well
10:42: so let's look at some different ways we
10:44: can affect this output
10:47: so first is we can do index equals false
10:51: and then that is
10:54: going to remove that 0 1 2
10:58: 3 going down the side see now
11:01: it starts right from the stock column so
11:03: this in this case we didn't care about
11:05: that index and so this is what we want
11:08: but still outputting to something sheet
11:10: 1
11:12: so we can control that part as well
11:15: so here we just also pass the sheet name
11:18: and then um
11:21: we are going to have the sheet named as
11:25: we put it
11:26: in python and we've got that coming over
11:29: to our excel workbook
11:34: um and
11:38: the um lab exercise on this
11:42: material um is
11:46: going to be here in the slides the last
11:50: slide of the panda section and there's
11:53: actually three different exercises here
11:56: contained within one so you can
11:57: click on the links to go to the other
11:59: exercises
12:01: um and this does require
12:05: using some files uh from the course site
12:07: so you can go directly to download those
12:09: or you can go to the resources slide and
12:12: download those from here
12:16: and as far as what you're required to
12:20: submit
12:21: or to get full credit on the lab
12:24: exercises
12:25: you need to do level one and level two
12:28: level three is an
12:29: optional exercise it uses uh
12:32: the advanced material in the jupiter
12:33: notebook um
12:35: so that's down here we're not going to
12:38: be
12:39: covering this in detail in the course
12:41: you can look at this
12:43: as an extension where we can write to
12:45: multiple different sheets
12:46: in the same workbook but we're not going
12:50: to be
12:50: covering that in detail so that's an
12:53: optional
12:54: lab exercise only one and two are
12:56: required here
12:58: and so one is pretty much reading in
13:00: this
13:01: financials workbook and do note that
13:05: this is an xls
13:06: file the old uh excel format and so
13:10: similarly in your code you'll have to
13:11: have xls not xlsx
13:15: uh read the income statement sheet and
13:17: then write that out to a new workbook
13:20: um and then the level two using the same
13:24: initial workbook now we're just going to
13:27: output to five different workbooks um
13:30: and do that without having to write the
13:32: to excel command over and over again
13:36: so that covers the integration
13:40: of excel and python using pandas
13:43: next time we're going to come back to
13:45: talk about how we can
13:47: do an even more more flexible
13:50: integration
13:51: using excel wings so thanks for
13:54: listening
13:54: and see you next time

00:02: hey everyone
00:03: this is nick dearmardis teaching you
00:05: financial modeling
00:07: today we're going to be talking about
00:08: combining excel and python
00:11: using excel wings this is part of our
00:14: lecture segment on combining excel in
00:16: python
00:18: so we already explored how to use
00:21: pandas to do simpler integrations of
00:24: excel in python
00:25: now we're going to look at the full
00:28: possible connection between the two
00:30: using the excel wings library
00:34: so excel wings gives
00:37: us a nice convenient way that we can
00:41: use excel from python and use python
00:43: from excel
00:44: and go back and forth between the two
00:48: so you don't actually need to use excel
00:50: wings it's
00:51: it's built on top of uh
00:54: the microsoft com api
00:58: so that's an api that microsoft
01:01: releases so that other software can
01:04: interact
01:05: with microsoft office products
01:11: but it's kind of a pain to use
01:14: uh directly in python
01:17: i uh was doing that myself before this
01:21: xl wings library really became a thing
01:24: uh years back i had to
01:28: build out some code that um was
01:32: i was driving an excel plugin from
01:33: python i was basically using
01:35: excel plugin to pull some data and then
01:37: i would use python to
01:40: switch it out for different companies
01:41: and keep re-running the plug-in
01:43: relaunching excel and all these things
01:47: so that was all possible without excel
01:48: wings but
01:50: it sure would have been quite a bit
01:51: easier had
01:53: excel wings been where it is today
01:57: when i had built out that project
02:01: so it is still under active development
02:04: just like
02:06: any of the um third-party packages that
02:08: we
02:09: use in the class um and
02:13: some parts of it are more stable than
02:15: others the part
02:16: that we're going to look at and use in
02:18: this course is very stable
02:20: uh i've never had any issues with it and
02:23: neither have i heard any issues from
02:25: anyone in the classes
02:27: um there are other ways to use it which
02:30: we'll talk about briefly and
02:33: some of those are newer and less stable
02:36: but
02:37: really interesting ways to integrate the
02:38: two
02:41: but the things are moving quickly in
02:43: this space
02:44: every day there are improvements
02:47: and so over time i expect all these to
02:50: become
02:50: stable and well accepted ways of
02:52: combining the two
02:55: so i keep saying there are different
02:57: ways to use excel wings
02:58: and combine the two so what are those
03:01: different ways so
03:05: of two main categories of ways that we
03:09: can
03:10: uh integrate excel in python one of cell
03:13: wings
03:14: the first here is manipulating excel
03:17: from
03:18: python so that's writing python code
03:21: to make excel do things
03:24: and that is where we're going to focus
03:27: in this course
03:30: and we're even focusing on a subset of
03:33: that
03:33: we're gonna focus on just bringing data
03:35: back and forth between excel
03:37: and python um and
03:40: orchestrating that from python
03:44: um but you can also
03:47: use this integration to do absolutely
03:50: anything that you would
03:51: be able to do in excel uh you want to
03:56: you know run run a create a data table
03:59: or
04:01: you know make a pivot table or whatever
04:04: anything that you can do in excel you
04:06: can do it from python
04:09: execute some python code and excel will
04:11: respond
04:12: in turn
04:16: the other main way that we can combine
04:18: the two is to run
04:20: python from excel so that's
04:23: using excel to orchestrate things and
04:25: running python code
04:27: and within that we have two different
04:29: categories here that are supported
04:31: in the excel wings library so one is
04:34: python as a vba replacement and the
04:37: other
04:38: is user defined functions so python is a
04:41: vba
04:41: replacement that means you know you have
04:45: an excel model
04:46: and you've hit the limits of what you're
04:49: able to do with basic cell you're
04:51: thinking okay i've got to write some vba
04:53: code
04:53: to accomplish what i'm trying to
04:55: accomplish well
04:57: this lets you write python code instead
05:00: and
05:01: just use that in place of using vba for
05:04: whatever you are going to do
05:07: so it basically gives you a vba function
05:10: run python which then will run python
05:13: code
05:13: in the background uh the other
05:17: main way of running python from excel is
05:20: using user-defined functions
05:22: and with user-defined functions
05:25: uh you basically create
05:28: a function in python
05:31: and you're able to call it from excel
05:35: so just like you have equals average in
05:38: excel
05:39: you can now have equals my function
05:42: and that my function is defined in
05:45: python code and it runs python code
05:47: it'll take the inputs from excel
05:50: run it through the python code and then
05:51: it will return
05:53: whatever you're returning it will take
05:54: that and bring it back into excel and
05:56: put the result in the cell so
05:59: really cool way of combining the two
06:02: and i think that um can really give you
06:05: the most power
06:06: um but those user-defined functions
06:10: are where uh there's still a lot of
06:12: active development going on
06:14: there's still um some bugs that they're
06:17: working through with them
06:18: and it's
06:22: uh something that you know i think a
06:25: couple years down the road they're going
06:26: to be
06:27: a lot more widespread but it's still
06:29: kind of the early days for the user to
06:31: find functions
06:32: um so lots of cool applications there
06:37: but we're just going to be focusing in
06:39: this course
06:40: on bringing data back and forth between
06:42: excel and python
06:44: because that is going to cover the vast
06:45: majority of
06:47: use cases for combining the two
06:53: and i would encourage you to google
06:55: excel wings documentation
06:57: and you can take a look at the other
06:58: approaches and give them a try on your
07:00: own as well
07:05: so the main integration that we're using
07:09: in this course
07:09: is that we can take
07:13: values numbers or data frames that we
07:17: have
07:17: lists numbers data frames strings
07:22: that we have in python and we can bring
07:25: those
07:25: into excel and we can take
07:28: data which we have in excel your
07:30: individual cells or whole tables
07:32: columns rows we can take that and we can
07:35: bring it into python
07:36: as lists or individual values or data
07:38: frames
07:41: um so we'll also just
07:44: briefly touch on uh you know one bit of
07:48: functionality that we'll use which um
07:52: the vba api that we have in python
07:55: um so this means that you can do
07:58: anything
07:59: in excel from your python code and we'll
08:02: we'll just look at recalculating the
08:04: workbook
08:05: as the one application here
08:08: but you can expand that to any possible
08:10: thing that you can do in excel
08:12: you can make your python code trigger
08:14: that in excel
08:19: and we'll look at how we can work with
08:20: entire tables at once
08:22: for convenience so here's a quick
08:26: example
08:27: of reading and writing values
08:30: um to and from excel so first
08:34: we have reading values from excel into
08:36: python and all of this is going to be
08:38: python code because we're talking about
08:40: doing all this from the python side
08:43: doing things from the excel side would
08:44: be the other way of integrating the two
08:49: and so here looking at reading values
08:51: from excel
08:53: here's the simplest example
08:56: um so we'll talk about this this sheet
08:59: object in the full jupiter notebook
09:02: example
09:03: we'll see how we get that but just
09:04: assume you have that already
09:06: then it's just dot range and you give it
09:09: whatever cell range
09:10: you want to get and then dot value and
09:14: that's going to get the value
09:15: from that cell in excel
09:18: and then you can save that into a
09:20: variable just like you can with any
09:23: other
09:23: expression um and
09:27: if you uh give it a range you know just
09:30: like you would type that range in excel
09:32: with the colon
09:33: and the cells on each side then
09:36: it will give you all the values which
09:40: exist in that range
09:41: and in the jupiter notebook example
09:42: we'll look at the format we get those
09:44: back
09:45: in and
09:49: we it also has a nice shortcut this
09:51: expand
09:52: function which basically says
09:55: you know start from g11 and then go
09:58: right and go down
09:59: until you reach the end of the table so
10:01: that lets you just take the
10:03: top left cell of a table and grab the
10:05: entire table
10:07: using expand
10:10: and then looking at writing values we
10:12: have numbers in python we want to bring
10:14: them into excel
10:17: so then it's just taking this expression
10:19: and and flipping it
10:21: so same thing that we did to pull the
10:24: value from excel now we have that on the
10:26: left hand side
10:27: of the equals we're assigning to it
10:30: and we just assign whatever value and
10:32: that's going to come into excel
10:34: and if we give it a
10:38: list then it's going to put multiple
10:40: values in multiple cells so 10 would go
10:42: in g11
10:44: and then 11 would go in h11 it would go
10:48: horizontally
10:49: to the next column
10:53: and then if you specify that range as a
10:56: vertical range then that will make that
10:58: same
10:59: so same values go vertically g11 and g12
11:02: will have those values and then if you
11:04: already have a data frame
11:06: it's already set up to work very nicely
11:09: with data frames
11:10: and so you can just assign a data frame
11:12: into a cell and then it's going to put
11:14: the entire table there
11:16: expanding outward from that cell
11:20: so that's the basic idea now let's go
11:22: ahead and take a look at the full
11:24: example
11:24: in the jupiter notebook um
11:28: so in addition to importing pandas we're
11:31: gonna need to work with data frames now
11:33: we also import excel wings
11:35: and there's one of these conventions
11:37: here for
11:38: excel wings as well people typically
11:41: import excel wings as
11:42: xw
11:46: um so what we didn't show in that
11:48: example is how you initially
11:50: get connected to the workbook in the
11:51: worksheet so
11:53: now we're going to look at that so
11:57: now that we have xw we can do xw.book to
12:00: get a workbook
12:02: and you give it the name of the workbook
12:04: or the full file path
12:05: if it's in a different folder
12:08: and if you don't already have the
12:11: workbook open it's automatically going
12:13: to open it
12:14: for you um so i can see now
12:18: that opened example workbook for me
12:21: and this is the example workbook that
12:25: we're
12:25: working with so i'm just going to put
12:28: that over there so we can see
12:30: what's going on
12:34: in the excel as well as the python so we
12:38: look at that book object it's a book
12:40: object and we can see the name of the
12:42: workbook
12:44: um and that's
12:47: an excel wings book object
12:50: um so now we're connected to the
12:53: workbook we want to connect to the
12:55: worksheet
12:56: um so if you do book doc sheets you can
12:59: see
13:00: uh the sheets which are in there here
13:02: example workbook has sheet one
13:06: and you can a neat way that they've set
13:10: up
13:10: uh these sheets and book objects
13:14: in uh xl wings is that they kind of work
13:18: both like lists and like dictionaries
13:20: you can access it either way
13:23: so if you give it a numerical index then
13:26: it's going to
13:28: pull it out by the order so here this is
13:31: going to get the first
13:32: worksheet from the workbook so now we
13:34: have that sheet1
13:36: worksheet or you can look it up by the
13:39: name like you would with a dictionary
13:42: and that will also get us to sheet1
13:45: and this is a sheet object that we have
13:47: here
13:49: um so typically
13:53: you're just going to have those two
13:54: things together
13:56: you would get the workbook and then you
13:58: would get the sheet from the workbook
14:00: and then everything else you would just
14:02: work with the sheet
14:07: so let's see what we can do with this
14:11: so we can see we have this something
14:13: over here
14:14: in a1 and if we look up from a1
14:19: uh in that sheet we want to get the
14:20: value that's going to give us that
14:23: something
14:23: right there and just to prove that's
14:27: working we can move over to a2
14:30: and that's getting nothing right because
14:32: there's nothing in that cell
14:34: but we can look at b1 and that's going
14:36: to get else so we are indeed pulling
14:38: values
14:39: from that excel workbook
14:43: and when we pull from a cell which has
14:46: no value we're going to get none
14:48: as a result so we can see that what
14:50: we're getting is none
14:52: that is indeed true because c1
14:55: has no value
14:58: so that's reading a single value now we
15:01: can write a single value so we're going
15:03: to write into a2 now we want to take the
15:05: value of 10 and write it in there
15:07: and we run this and we can see that has
15:09: now showed up
15:10: in the excel workbook so pretty neat to
15:13: see
15:14: all that happening live
15:17: um and then we can read multiple values
15:21: at once so we can give a range
15:23: of cells here a1 to a2 and we want to
15:27: get that value
15:28: and that's going to get us those values
15:30: in a list first
15:31: something and then the 10.
15:35: um and we can target this a1 to b1
15:38: instead and we'll get something
15:40: else uh each of those coming as an item
15:42: in the list
15:45: now it does get a little bit more
15:46: complicated when we want to do a
15:48: two-dimensional range
15:50: so here a1 to b2 that's
15:54: getting all four of these cells so the
15:56: way that
15:57: excel wings is going to represent that
15:59: by default
16:00: is with a list of lists so within this
16:04: outer list
16:05: we have lists and each one of these
16:08: lists
16:08: is representative of a row um
16:12: so we can see the first row something
16:14: else and then we can see
16:16: the second row 10 and then a blank cell
16:18: so none
16:20: um and it can be a little bit
16:22: complicated to work with that
16:24: and the creators of excel wings realize
16:27: this
16:28: so we can also pass
16:31: options to this and we can pass the
16:35: option that we would like to get a data
16:36: frame
16:37: with the result instead of a list of
16:39: lists
16:40: so if we just do the same thing
16:44: but put this options data frame before
16:47: the value now you can see it comes
16:50: as a data frame but there is
16:53: uh something weird here that
16:56: um we have else here
17:00: as the name of a column and then we have
17:03: something as the name of the index and
17:05: then we have 10
17:06: as a value of the index and we have a
17:08: single cell data frame which has none in
17:10: it
17:11: so maybe not quite what we wanted um
17:14: maybe it is
17:15: but we have options to control how we
17:18: pull it in
17:20: so pulling from that same range again
17:22: with data frame
17:23: just now additionally in options we're
17:25: passing index equals false
17:27: that means assume that the data in excel
17:29: does not have
17:30: an index column on it um you can go
17:33: ahead and just
17:34: automatically make the index be this 0 1
17:36: 2
17:37: auto incrementing thing and when we do
17:40: that then we see that something and else
17:42: come as the columns and 10 and none
17:46: come as the values for those
17:50: um and we can also pass
17:55: header equals false so that these
17:58: uh the first values which are there are
18:01: going to come
18:01: into the data frame rather than as
18:03: columns of the data frame
18:07: so now it auto named the column is zero
18:10: and we have something else as values
18:13: though something in 10 did come back
18:15: over to the index because we didn't
18:17: include index equals false
18:19: so if we include both of those then we
18:21: see the index is auto
18:22: 0 1 the columns are auto 0 1 and we have
18:26: those four values
18:27: as cells of the data frame so which you
18:30: pick in between these
18:31: is just going to depend on how you have
18:32: your data in excel if it has headers
18:35: um then you would want to leave it as it
18:38: is if it doesn't have headers you would
18:39: want to pass header equals false
18:43: and generally you're going to want to
18:44: pass index equals false unless you want
18:46: to have
18:47: your left most values as the mx of the
18:49: data frame and then
18:50: you can exclude that
18:55: so um then we can look at writing
18:58: multiple values
18:59: so um here if we just take a single
19:03: value and we assign it
19:04: across a range here d1 to d2 we're going
19:07: to see that that same value
19:09: comes into each one of the cells in that
19:11: range
19:12: um and then uh
19:16: so a5 to b5 a5 to b5 that's here
19:20: we want to put the 10 and 11 to there
19:24: so same size range one dimensional
19:27: it's going to be able to put those
19:28: values there
19:31: and then if you did a vertical range uh
19:35: that would work as well so we can go a7
19:37: to a8
19:39: and that should uh oh that does
19:42: it does always go horizontally you
19:45: cannot just write vertically like that
19:47: um if you want to write vertically
19:50: what you have to do is go to this list
19:53: of lists
19:54: pattern and put each of these values
19:58: in lists so then
20:02: we can see that the values come
20:04: vertically um
20:07: and as a
20:11: one-liner for that uh i'm just double
20:13: checking we don't have that later in
20:15: here
20:16: nope so one liner for that if you
20:20: already have your 10 and 11 lists
20:21: and you want to put it vertically
20:24: starting at
20:25: a10 so
20:28: my list is 10 12.
20:33: then you can use a list comprehension to
20:35: convert it into that structure
20:38: um so you're just taking each element of
20:41: the list and wrapping it
20:43: into a list and then we should see from
20:46: a 10 down to a11 that these numbers
20:49: then come there so you can use this
20:52: pattern
20:53: if you want to output vertically
20:57: um and then to output to an entire
21:00: two two-dimensional range then we do
21:02: this list of list patterns so here
21:04: going from a7 to b8
21:08: and you'll notice also that whatever is
21:10: there it's going to overwrite it
21:12: as well so now we have
21:15: this 12 13 14 15 12 and 13 in the first
21:18: row 14 and 15 in the second row
21:21: in that a7 and b8 range
21:27: so then um we can read entire
21:31: rows or columns
21:35: using the expand functionality in excel
21:38: wings
21:39: so expand it can automat if you don't
21:43: pass anything it automatically goes
21:44: down and right but you can also pass
21:46: right or down to just go in that
21:48: direction
21:49: um so starting from this a7 if we expand
21:52: to the right
21:54: then we're going to get the 12 and the
21:55: 13 because it started from here and
21:57: expanded right to get both of those
22:00: but if we target the same cell but
22:02: expand down
22:04: then we're going to get 12 and 14
22:06: because it's going to start from here
22:07: and expand down
22:09: and if you don't pass anything into
22:11: expand then it's going to get all of
22:12: this
22:14: with the list of lists kind of data
22:16: structure
22:18: but you can combine expand with options
22:21: um
22:23: and that lets us put this
22:26: two-dimensional data into a data frame
22:28: so using the same
22:30: stuff we learned from the above section
22:32: we're making that a data frame
22:34: excluding the index excluding the header
22:37: and we can see that we get
22:41: the 12 13 14 15 with the auto
22:44: incrementing
22:45: columns and
22:49: index
22:52: so we can take an entire table from
22:55: python we can take a python data frame
22:58: um and we can write that into
23:01: excel as well it just directly works
23:04: uh here i already have something in a10
23:07: i didn't need to
23:08: delete it out but just so it's clear
23:11: i'm going to write into a10 there with
23:14: data frame
23:15: and we can see that all of that comes in
23:17: there
23:19: now you will notice you know just like
23:21: when we're
23:22: reading into a data frame from excel
23:26: that there are options around
23:30: the index and the header
23:34: because by default it's going to put the
23:37: index
23:37: and the columns there so we said start
23:40: from a 10 we want to output this
23:42: 16 17 18 19 but it also took this
23:45: 0 and 1 uh index and column names and
23:49: brought them in here as well
23:51: um so now we're going to try here at 8
23:55: 14
23:55: and if we exclude the index and the
23:57: header assign that data frame again
24:00: now it's just going to take those values
24:03: from the data frame
24:04: rather than including the um
24:07: index and column names
24:14: so um there's an important
24:17: gotcha when you want to work with
24:20: integers
24:21: uh and pulling them from excel into
24:23: python
24:25: and most of the time in python we don't
24:27: really care whether it's an integer
24:28: or a floating point number but
24:32: in certain cases such as when we want to
24:34: do a number of
24:35: loops we do care whether it's
24:38: an integer or a floating point number
24:42: so um we're gonna pull in
24:46: from f5 so f5 is this five here
24:50: and we're going to um pull in
24:53: the value from there as our number of
24:55: loops variable
24:56: and you can see it's just a plain five
24:58: there's no decimals on it or anything
25:00: here
25:00: but we can see when it came into uh
25:04: python that we got 5.0
25:08: and we look at the type it is indeed a
25:11: float not an integer
25:13: and so that means if you just take that
25:15: and you just try and loop with it
25:17: then we're going to get this error float
25:18: object cannot be interpreted as an
25:20: integer
25:21: um so what we have to do is convert that
25:25: into an integer before we can loop over
25:28: it
25:29: so we just wrap that num loops in and in
25:32: and then now it's an integer and so
25:36: we can do our loops and everything on it
25:43: um there are also some gotchas
25:47: around formatting in excel when you try
25:49: and read that back into python
25:52: um so here let's look at the f1 value
25:55: it's formatted as currency
25:57: in excel let's pull that into python
26:00: you can see it comes in as decimal 10.25
26:05: and if you just try and add a number
26:08: to this decimal we're going to get
26:10: unsupported operand type
26:12: decimal and float so what we can do
26:16: same thing as we did with the integers
26:18: you just convert it
26:20: so just convert that to a float and then
26:23: we can do the math with it just fine
26:27: um and then
26:31: f3 there we have the accounting
26:33: formatting
26:34: it's going to be the same thing but if
26:36: you just take the float of that
26:38: you're going to be able to get the
26:40: number back from the decimal
26:43: um and
26:46: percentages do thankfully just work out
26:49: of the box just fine
26:50: it pulls it in as a float in the correct
26:53: format
26:56: and then if you have a date it's going
26:58: to pull it
26:59: as the date time type in python so
27:02: that's a built-in
27:03: type that we haven't talked about in the
27:05: course
27:06: but this is a standard way to work with
27:09: dates in python
27:10: and it works perfectly fine with pandas
27:13: as well
27:18: so the last thing that we're going to
27:19: look at here um
27:22: is recalculating the workbook
27:26: so typically you're not going to need to
27:29: do this
27:31: because when you
27:36: write an input into excel it's going to
27:40: recalculate
27:42: the workbook automatically
27:46: so let me just write a value here
27:49: um a2 let's make that 11.
27:53: you can see that the random value over
27:55: here recalculated
27:56: when i did that i'm just running this
27:58: python code over here
28:00: and you can see the excel workbook
28:01: recalculating over there
28:03: so anytime that you write something into
28:06: excel
28:06: it's going to recalculate um and so you
28:09: don't normally need to explicitly
28:11: recalculate
28:12: um the only time where you're going to
28:14: need to do that
28:16: is if you want to keep re-running the
28:18: excel model
28:19: when you're not changing any inputs and
28:22: the case where that
28:23: generally applies is when you have
28:25: internal randomness
28:26: in your excel model such that each time
28:29: you run the excel model with the same
28:30: inputs
28:31: you're getting a different output each
28:33: time so in that case
28:35: you might want to have your python code
28:37: do a loop
28:38: where it uh you know recalculates the
28:42: workbook and then extracts the output
28:43: from excel
28:44: and saves that in the list and then
28:46: recalculates the workbook extracts it
28:47: from excel saves it in the list and does
28:49: that for some number of iterations
28:53: so how can we do this recalculation so
28:57: we do have to work with the app object
29:00: instead of the book or sheet object um
29:03: so this one liner here can get us access
29:06: to the current
29:08: uh application object excel app
29:12: and then on that you can do app.api
29:16: dot recalculate full and you notice when
29:19: i run that that the random value in the
29:22: excel sheet
29:23: keeps recalculating
29:26: um and
29:30: you might try this on your machine and
29:32: find it doesn't work
29:34: and that's because the api is actually
29:37: slightly different
29:38: on x on
29:42: mac versus windows so this command
29:44: should work on mac while this one works
29:46: on windows
29:49: so in case you need to do this in your
29:51: model
29:52: i included a convenience function for
29:55: you
29:56: uh recalculate workbook you can just
29:58: copy this function
29:59: into your model um and you just call
30:02: recalculate workbook
30:03: and you see i call that it is
30:05: recalculating and what this does is that
30:07: it gets the app
30:08: excel wings app um and then it's going
30:11: to check
30:11: what operating system you're on if
30:13: you're on windows it's going to call
30:14: that
30:15: first calculate full if you're on mac
30:18: it's going to call that just calculate
30:20: and otherwise it's it's going to raise
30:23: an error if it can't properly detect
30:24: your operating system
30:26: um so you can just take this into your
30:30: model and use it should you need to
30:32: recalculate
30:33: uh your model because it's an internal
30:35: randomness excel model that you're
30:36: trying to run
30:37: repeatedly from python
30:41: that covers how to integrate uh python
30:44: and excel using excel wings
30:47: and there is a lab exercise multiple lab
30:50: exercises on this material
30:52: as well so
30:56: there are actually four lab exercises
30:59: here the different levels
31:01: um and the fourth one is
31:04: optional one two and three are what's
31:06: required to submit for the course
31:10: and number one here and with all the
31:13: exercises
31:14: you're going to download this uh excel
31:16: wings lab file from the course site or
31:18: from the resources slide here this
31:20: directory will let you download it
31:24: and the first exercise is to read
31:28: the values in the workbook or the
31:30: worksheet from column a
31:32: and then write them into column b using
31:35: python and excel wings the second
31:38: exercise is to get the value
31:40: from c9 and multiply it by 2.5
31:43: in python and show the result and the
31:46: third exercise
31:48: is to read this table in a python do
31:50: some math on it
31:52: and output it back into excel and
31:55: make sure that you can uh match up the
31:58: index and header to the same format as
32:01: the original
32:02: table so that covers
32:06: uh everything we're going to look at in
32:08: the course with combining excel
32:10: and python and this also concludes
32:14: all the material um that you would need
32:17: that everyone should need for every
32:18: possible variation of the project 2
32:22: model so thanks for listening
32:26: and see you next time