Combining Excel and Python¶
Use Python from Excel and use Excel from Python. Learn to use the two tools together flexibly to enable using the most effective tool for each portion of the model.
Resources¶
Introduction to Combining Excel and Python¶
Notes¶
At this point in the course, you should feel comfortable using both Python and Excel to create models to solve problems
Now it is time to learn how to combine the two tools for the maximum flexibility, power, and convenience
We will cover two approaches to integrating the two: using Pandas and using xlwings
We are also about to learn Monte Carlo simulation, which can be done easily in Python but would require using VBA or an extension in Excel. Using this combination we can have the model in Excel and run Monte Carlo simulations on it in Python
The Pandas approach is simpler but is much more limited, basically you can read in Excel workbooks and you can output an entire workbook or sheet
The xlwings approach gets a bit more complicated but allows to have a connection between Excel and Python and transfer individual values or entire tables back and forth with an existing workbook
Transcript¶
- hey everyone
- nick dear burtis here teaching you
- financial modeling today
- we're going to be starting a new lecture
- segment
- on combining excel and python so in this
- video we're just going to introduce the
- topic
- and talk about why we're combining the
- two why we're just covering this now
- etc so
- we so far have
- built out a competency in working
- with both excel and python now
- at this point in the course you should
- feel comfortable
- working with both in excel and python to
- build out models for whatever problem
- that you're trying to solve and now that
- we feel comfortable
- with each of the two tools we can start
- looking at how we can
- combine the two tools
- um because excel and python they both
- have different advantages and
- disadvantages
- and for any particular problem that
- you're going to tackle
- uh one of the two tools may be better
- suited
- for different parts of the problem
- and so it's really an ideal setup to be
- able to flexibly switch back and forth
- between the two
- to be able to use whichever tool is
- most effective for the job
- so that is a big thing that
- we're building towards in this course is
- not only knowing how to build financial
- models in both excel and python
- but also knowing which one to use and
- when
- so that you can most effectively work
- on the problems that you're trying to
- solve
- different problems we can break them
- down and do some parts with
- each and um this will probably also
- be a breath of fresh air for those that
- have really struggled with the python
- side of things and
- want to do as much as possible in excel
- well this allows you to
- build most of your model model in excel
- and then
- add on these additional capabilities
- that python has
- to your excel model
- so now you're going to be able to just
- flexibly go back and forth between the
- two
- and this is going to be really useful as
- we go to learning monte carlo simulation
- the next major
- topic because there's not
- a easy way to do that in excel without
- going to vba so we can use python to run
- monte carlo simulations on both our
- python
- and excel models
- and as far as how we can combine
- python and excel we'll talk about two
- major approaches
- we'll talk about using pandas and we'll
- also talk about
- excel wings as two libraries which
- are going to be able to help us combine
- the two
- where pandas is a simpler integration
- where we can just
- pass a workbook back and forth basically
- read that in from a workbook output to a
- workbook
- where as when we use excel wings then we
- can really flexibly go back and forth
- between the two
- write individual values uh back and
- forth between excel and python
- run uh excel functionality from python
- and many more use cases so
- we'll explore each of these two methods
- in turn
- and that will be the focus of the
- following videos
- so thanks for listening and see you next
- time
Combining Excel and Python using Pandas¶
Notes¶
If you have a Python model and you just want to load some data in from Excel, Pandas is probably your best choice
If you have an Excel model and you collect the data using Python, this is also a good choice
If you want to have some parts of your model in Excel and some parts in Python, you should probably look to xlwings
BE CAREFUL WHEN WRITING TO WORKBOOKS as it will replace what is there. It could overwrite your Excel model. THERE IS NO UNDO (back up your work)
Resources¶
Transcript¶
- hey everyone
- nick dearmardis here teaching you
- financial modeling today we're going to
- be talking about
- combining excel in python using pandas
- and this is part of our lecture series
- on combining excel and python
- so we said that there are two main ways
- that we can
- combine excel in python that we're going
- to explore in this course that's using
- pandas and using excel wings
- so we'll come back in the following
- video to explore excel wings
- but in this video we're going to look at
- the pandas
- way of going between excel and python
- so this is generally a simpler
- integration which
- is not does not have nearly as many
- features
- but it's very fast and has a very simple
- api that we can work with
- so pandas we get data frames we can do
- all this
- visualization and different analysis and
- it also
- can go in and out of different data
- formats so excel
- files are one of those formats
- so we can take a data frame and we can
- output that to an excel workbook and
- we can take an excel workbook and we can
- read that in
- to append this data frame so that's
- pretty much the extent of the
- integration between the two
- and for a lot of cases this is all that
- you need
- if you have a python model and you just
- need to get some data which is stored in
- excel spreadsheet
- then this is perfect um or
- if uh you have an excel model
- and you use python to collect the data
- maybe through
- web scraping or pulling from a database
- or apis or
- something which is going to be easier to
- approach in python
- you just want to take that data from
- python and be able to load it into your
- excel model
- as a full worksheet this is also
- a good way to go about it but for any
- use case which is going to require a
- tighter integration things going back
- and forth between
- excel and python or taking individual
- values rather than an entire
- worksheet or workbook at once then excel
- wings
- should be the way to go
- but um oftentimes
- we either have a python or excel model
- and it's
- easy just to transfer the data between
- using pandas
- so what does this actually look like
- we'll look at a quick example here so
- the first block here
- is for reading excel files so
- for reading excel files uh we're going
- to use a method of panis library read
- excel
- so assume you've already imported pandas
- as pd
- then you can do pd.read excel and you
- give it the name
- or file path of your spreadsheet
- if it's in the same folder you can just
- give the name and if it's somewhere else
- on your computer
- then you would want to put the full file
- path here
- and this sheet name is optional you can
- do that to say which
- specific sheet you want to get out of
- the workbook
- and if you don't pass it then it's just
- going to take the first
- sheet out of the workbook
- and it's always going to read in the
- entire worksheet
- so if you have multiple different tables
- in the worksheet
- that can be another reason to go to
- using excel wings you can more flexibly
- grab
- individual things out of a worksheet
- whereas pandas is going to read the
- entire worksheet as a data frame so
- if you have anything other than just one
- table in that worksheet
- then pandas is going to be picking up a
- lot of blank cells and things like that
- and you'll have to do additional cleanup
- so it's definitely easiest if you just
- have
- one tab one table on the worksheet
- and then for writing in that case we
- would already have a data frame created
- and then we can use the method of that
- data frame
- to excel to output to an excel workbook
- um and so there similarly to reading you
- give
- the name or file path of the
- output so if you just put a name it's
- going to go in the same folder
- if you put a full file path then you can
- go wherever you want
- and you want to give the
- name of the sheet that we're going to
- output to
- and the last thing we're including here
- is index equals false
- so index in penis is that
- thing which goes on the left-hand side
- of
- the data frame which by default is just
- going to be 0 1
- 2 like auto incrementing um
- so if you do just have that 0 1 2 and
- you don't care about it
- pass this index equals false if you have
- actually set the index to something so
- the index has meaningful values in it
- then you don't want to put that index
- equals false
- assuming you want to have that in the
- output as well
- and i want to
- definitely highlight this warning this
- is very very important
- when you write to an excel workbook
- using pandas it's going to replace
- whatever is there and it's going to
- whatever was there before is going to be
- gone
- and there is no undo this is true
- in general with programming if you tell
- the computer
- to do something it's going to do it it's
- not going to ask you
- are you sure you want to do this uh it's
- only if you're using an app which has
- built in those kind of protections
- um but with a programming language it's
- going to do whatever you tell it to
- and so if you tell it to output to a
- workbook
- which already has your full excel model
- in it
- you really meant to just output to one
- sheet of that workbook
- but you accidentally output it to the
- entire workbook it's just going to erase
- everything that you had in your model
- it's going to be gone forever
- so be really careful about this make
- sure to back up your work
- and generally uh be really careful
- if you're going to try and write a sheet
- into an existing excel
- model um it's better generally to just
- write to a separate workbook
- because then uh that workbook is always
- just being generated by the code
- and you don't really care about what's
- in there it's just getting
- replaced whenever you run the code
- so just be careful about this
- you cannot undo it once you have written
- it over so you get a very bad day
- if you made a mistake with this so
- that's why i always
- among other reasons always recommend
- that people back up their work
- regularly uh you know you can use a
- service like like dropbox or something
- to do it automatically or you can
- you know have your own manual system of
- copying files
- whatever you do just be careful when you
- write
- to excel workbooks and this you know
- this is the only
- uh part of the course where we've
- actually outputted a file um
- this is really a general caution about
- any time you're going to output a file
- in any programming language it's going
- to overwrite whatever is there
- so just be careful about this
- so let's go look at a quick example of
- how we can work with pandas to read and
- write
- excel files
- so we'll hop over to the jupyter
- notebook here read write
- excel pandas and we can see it works
- with
- a workbook called stock data so i'm just
- going to quickly
- open up that stock data excel workbook
- so that we can see what the contents are
- and you can see it's got two worksheets
- here the first sheet and the second
- sheet
- first sheet got microsoft and apple
- stock prices
- over time second sheet amazon and
- stock prices over time
- so of course first we want to import
- pandas
- and then we can read that data
- into a data frame so we read the stock
- data file we didn't tell it any sheet or
- anything
- and we can see that it loaded the first
- sheet by default which has microsoft
- and apple and now we have all that and a
- data frame
- um but if we want that second sheet then
- we just pass receipt name
- here the second sheet whatever the name
- of the sheet
- and now we get the amazon and facebook
- worksheet
- so now coming to outputting
- the data frame to an excel file so let's
- look at
- let's do something with the data frame
- so that we know it's different uh
- here we're just adding a column which
- has the string stuff
- in it a custom column
- and now we're going to go ahead and
- output this
- so i'm going to run this to excel
- creating newbook.xlsx
- and let's take a look at what we got in
- the new book
- so we can see um
- that we have the data which uh
- was in the data frame has now come over
- to the excel worksheet
- the stock uh tickers prices and dates
- um and we also have this additional
- column that we added
- you'll also notice um that it just put
- it on a sheet one
- that's going to be the default sheet
- name
- and you can see that it brought over
- this index 0 1 2
- 3 4 5 6 7 as well
- so let's look at some different ways we
- can affect this output
- so first is we can do index equals false
- and then that is
- going to remove that 0 1 2
- 3 going down the side see now
- it starts right from the stock column so
- this in this case we didn't care about
- that index and so this is what we want
- but still outputting to something sheet
- 1
- so we can control that part as well
- so here we just also pass the sheet name
- and then um
- we are going to have the sheet named as
- we put it
- in python and we've got that coming over
- to our excel workbook
- um and
- the um lab exercise on this
- material um is
- going to be here in the slides the last
- slide of the panda section and there's
- actually three different exercises here
- contained within one so you can
- click on the links to go to the other
- exercises
- um and this does require
- using some files uh from the course site
- so you can go directly to download those
- or you can go to the resources slide and
- download those from here
- and as far as what you're required to
- submit
- or to get full credit on the lab
- exercises
- you need to do level one and level two
- level three is an
- optional exercise it uses uh
- the advanced material in the jupiter
- notebook um
- so that's down here we're not going to
- be
- covering this in detail in the course
- you can look at this
- as an extension where we can write to
- multiple different sheets
- in the same workbook but we're not going
- to be
- covering that in detail so that's an
- optional
- lab exercise only one and two are
- required here
- and so one is pretty much reading in
- this
- financials workbook and do note that
- this is an xls
- file the old uh excel format and so
- similarly in your code you'll have to
- have xls not xlsx
- uh read the income statement sheet and
- then write that out to a new workbook
- um and then the level two using the same
- initial workbook now we're just going to
- output to five different workbooks um
- and do that without having to write the
- to excel command over and over again
- so that covers the integration
- of excel and python using pandas
- next time we're going to come back to
- talk about how we can
- do an even more more flexible
- integration
- using excel wings so thanks for
- listening
- and see you next time
Combining Excel and Python using xlwings¶
Notes¶
xlwings is a package that makes it quite easy to combine Excel and Python in ways that should work for nearly every use case
This can be done without xlwings using the Microsoft COM API in Python, but xlwings is far more convenient
We are focusing here on only manipulating Excel from Python, but I encourage you to explore running Python from Excel on your own time
Now with xlwings, anything that you can do in Excel, you can make it happen from Python
It is easy to transfer individual values, ranges, and tables back and forth between Excel and Python
For those who have a lot of difficulty with Python and feel comfortable in Excel, xlwings allows building out the core model in Excel and adding extensions such as Monte Carlo simulation, sensitivity analysis, and scenario analysis in Python
Transcript¶
- hey everyone
- this is nick dearmardis teaching you
- financial modeling
- today we're going to be talking about
- combining excel and python
- using excel wings this is part of our
- lecture segment on combining excel in
- python
- so we already explored how to use
- pandas to do simpler integrations of
- excel in python
- now we're going to look at the full
- possible connection between the two
- using the excel wings library
- so excel wings gives
- us a nice convenient way that we can
- use excel from python and use python
- from excel
- and go back and forth between the two
- so you don't actually need to use excel
- wings it's
- it's built on top of uh
- the microsoft com api
- so that's an api that microsoft
- releases so that other software can
- interact
- with microsoft office products
- but it's kind of a pain to use
- uh directly in python
- i uh was doing that myself before this
- xl wings library really became a thing
- uh years back i had to
- build out some code that um was
- i was driving an excel plugin from
- python i was basically using
- excel plugin to pull some data and then
- i would use python to
- switch it out for different companies
- and keep re-running the plug-in
- relaunching excel and all these things
- so that was all possible without excel
- wings but
- it sure would have been quite a bit
- easier had
- excel wings been where it is today
- when i had built out that project
- so it is still under active development
- just like
- any of the um third-party packages that
- we
- use in the class um and
- some parts of it are more stable than
- others the part
- that we're going to look at and use in
- this course is very stable
- uh i've never had any issues with it and
- neither have i heard any issues from
- anyone in the classes
- um there are other ways to use it which
- we'll talk about briefly and
- some of those are newer and less stable
- but
- really interesting ways to integrate the
- two
- but the things are moving quickly in
- this space
- every day there are improvements
- and so over time i expect all these to
- become
- stable and well accepted ways of
- combining the two
- so i keep saying there are different
- ways to use excel wings
- and combine the two so what are those
- different ways so
- of two main categories of ways that we
- can
- uh integrate excel in python one of cell
- wings
- the first here is manipulating excel
- from
- python so that's writing python code
- to make excel do things
- and that is where we're going to focus
- in this course
- and we're even focusing on a subset of
- that
- we're gonna focus on just bringing data
- back and forth between excel
- and python um and
- orchestrating that from python
- um but you can also
- use this integration to do absolutely
- anything that you would
- be able to do in excel uh you want to
- you know run run a create a data table
- or
- you know make a pivot table or whatever
- anything that you can do in excel you
- can do it from python
- execute some python code and excel will
- respond
- in turn
- the other main way that we can combine
- the two is to run
- python from excel so that's
- using excel to orchestrate things and
- running python code
- and within that we have two different
- categories here that are supported
- in the excel wings library so one is
- python as a vba replacement and the
- other
- is user defined functions so python is a
- vba
- replacement that means you know you have
- an excel model
- and you've hit the limits of what you're
- able to do with basic cell you're
- thinking okay i've got to write some vba
- code
- to accomplish what i'm trying to
- accomplish well
- this lets you write python code instead
- and
- just use that in place of using vba for
- whatever you are going to do
- so it basically gives you a vba function
- run python which then will run python
- code
- in the background uh the other
- main way of running python from excel is
- using user-defined functions
- and with user-defined functions
- uh you basically create
- a function in python
- and you're able to call it from excel
- so just like you have equals average in
- excel
- you can now have equals my function
- and that my function is defined in
- python code and it runs python code
- it'll take the inputs from excel
- run it through the python code and then
- it will return
- whatever you're returning it will take
- that and bring it back into excel and
- put the result in the cell so
- really cool way of combining the two
- and i think that um can really give you
- the most power
- um but those user-defined functions
- are where uh there's still a lot of
- active development going on
- there's still um some bugs that they're
- working through with them
- and it's
- uh something that you know i think a
- couple years down the road they're going
- to be
- a lot more widespread but it's still
- kind of the early days for the user to
- find functions
- um so lots of cool applications there
- but we're just going to be focusing in
- this course
- on bringing data back and forth between
- excel and python
- because that is going to cover the vast
- majority of
- use cases for combining the two
- and i would encourage you to google
- excel wings documentation
- and you can take a look at the other
- approaches and give them a try on your
- own as well
- so the main integration that we're using
- in this course
- is that we can take
- values numbers or data frames that we
- have
- lists numbers data frames strings
- that we have in python and we can bring
- those
- into excel and we can take
- data which we have in excel your
- individual cells or whole tables
- columns rows we can take that and we can
- bring it into python
- as lists or individual values or data
- frames
- um so we'll also just
- briefly touch on uh you know one bit of
- functionality that we'll use which um
- the vba api that we have in python
- um so this means that you can do
- anything
- in excel from your python code and we'll
- we'll just look at recalculating the
- workbook
- as the one application here
- but you can expand that to any possible
- thing that you can do in excel
- you can make your python code trigger
- that in excel
- and we'll look at how we can work with
- entire tables at once
- for convenience so here's a quick
- example
- of reading and writing values
- um to and from excel so first
- we have reading values from excel into
- python and all of this is going to be
- python code because we're talking about
- doing all this from the python side
- doing things from the excel side would
- be the other way of integrating the two
- and so here looking at reading values
- from excel
- here's the simplest example
- um so we'll talk about this this sheet
- object in the full jupiter notebook
- example
- we'll see how we get that but just
- assume you have that already
- then it's just dot range and you give it
- whatever cell range
- you want to get and then dot value and
- that's going to get the value
- from that cell in excel
- and then you can save that into a
- variable just like you can with any
- other
- expression um and
- if you uh give it a range you know just
- like you would type that range in excel
- with the colon
- and the cells on each side then
- it will give you all the values which
- exist in that range
- and in the jupiter notebook example
- we'll look at the format we get those
- back
- in and
- we it also has a nice shortcut this
- expand
- function which basically says
- you know start from g11 and then go
- right and go down
- until you reach the end of the table so
- that lets you just take the
- top left cell of a table and grab the
- entire table
- using expand
- and then looking at writing values we
- have numbers in python we want to bring
- them into excel
- so then it's just taking this expression
- and and flipping it
- so same thing that we did to pull the
- value from excel now we have that on the
- left hand side
- of the equals we're assigning to it
- and we just assign whatever value and
- that's going to come into excel
- and if we give it a
- list then it's going to put multiple
- values in multiple cells so 10 would go
- in g11
- and then 11 would go in h11 it would go
- horizontally
- to the next column
- and then if you specify that range as a
- vertical range then that will make that
- same
- so same values go vertically g11 and g12
- will have those values and then if you
- already have a data frame
- it's already set up to work very nicely
- with data frames
- and so you can just assign a data frame
- into a cell and then it's going to put
- the entire table there
- expanding outward from that cell
- so that's the basic idea now let's go
- ahead and take a look at the full
- example
- in the jupiter notebook um
- so in addition to importing pandas we're
- gonna need to work with data frames now
- we also import excel wings
- and there's one of these conventions
- here for
- excel wings as well people typically
- import excel wings as
- xw
- um so what we didn't show in that
- example is how you initially
- get connected to the workbook in the
- worksheet so
- now we're going to look at that so
- now that we have xw we can do xw.book to
- get a workbook
- and you give it the name of the workbook
- or the full file path
- if it's in a different folder
- and if you don't already have the
- workbook open it's automatically going
- to open it
- for you um so i can see now
- that opened example workbook for me
- and this is the example workbook that
- we're
- working with so i'm just going to put
- that over there so we can see
- what's going on
- in the excel as well as the python so we
- look at that book object it's a book
- object and we can see the name of the
- workbook
- um and that's
- an excel wings book object
- um so now we're connected to the
- workbook we want to connect to the
- worksheet
- um so if you do book doc sheets you can
- see
- uh the sheets which are in there here
- example workbook has sheet one
- and you can a neat way that they've set
- up
- uh these sheets and book objects
- in uh xl wings is that they kind of work
- both like lists and like dictionaries
- you can access it either way
- so if you give it a numerical index then
- it's going to
- pull it out by the order so here this is
- going to get the first
- worksheet from the workbook so now we
- have that sheet1
- worksheet or you can look it up by the
- name like you would with a dictionary
- and that will also get us to sheet1
- and this is a sheet object that we have
- here
- um so typically
- you're just going to have those two
- things together
- you would get the workbook and then you
- would get the sheet from the workbook
- and then everything else you would just
- work with the sheet
- so let's see what we can do with this
- so we can see we have this something
- over here
- in a1 and if we look up from a1
- uh in that sheet we want to get the
- value that's going to give us that
- something
- right there and just to prove that's
- working we can move over to a2
- and that's getting nothing right because
- there's nothing in that cell
- but we can look at b1 and that's going
- to get else so we are indeed pulling
- values
- from that excel workbook
- and when we pull from a cell which has
- no value we're going to get none
- as a result so we can see that what
- we're getting is none
- that is indeed true because c1
- has no value
- so that's reading a single value now we
- can write a single value so we're going
- to write into a2 now we want to take the
- value of 10 and write it in there
- and we run this and we can see that has
- now showed up
- in the excel workbook so pretty neat to
- see
- all that happening live
- um and then we can read multiple values
- at once so we can give a range
- of cells here a1 to a2 and we want to
- get that value
- and that's going to get us those values
- in a list first
- something and then the 10.
- um and we can target this a1 to b1
- instead and we'll get something
- else uh each of those coming as an item
- in the list
- now it does get a little bit more
- complicated when we want to do a
- two-dimensional range
- so here a1 to b2 that's
- getting all four of these cells so the
- way that
- excel wings is going to represent that
- by default
- is with a list of lists so within this
- outer list
- we have lists and each one of these
- lists
- is representative of a row um
- so we can see the first row something
- else and then we can see
- the second row 10 and then a blank cell
- so none
- um and it can be a little bit
- complicated to work with that
- and the creators of excel wings realize
- this
- so we can also pass
- options to this and we can pass the
- option that we would like to get a data
- frame
- with the result instead of a list of
- lists
- so if we just do the same thing
- but put this options data frame before
- the value now you can see it comes
- as a data frame but there is
- uh something weird here that
- um we have else here
- as the name of a column and then we have
- something as the name of the index and
- then we have 10
- as a value of the index and we have a
- single cell data frame which has none in
- it
- so maybe not quite what we wanted um
- maybe it is
- but we have options to control how we
- pull it in
- so pulling from that same range again
- with data frame
- just now additionally in options we're
- passing index equals false
- that means assume that the data in excel
- does not have
- an index column on it um you can go
- ahead and just
- automatically make the index be this 0 1
- 2
- auto incrementing thing and when we do
- that then we see that something and else
- come as the columns and 10 and none
- come as the values for those
- um and we can also pass
- header equals false so that these
- uh the first values which are there are
- going to come
- into the data frame rather than as
- columns of the data frame
- so now it auto named the column is zero
- and we have something else as values
- though something in 10 did come back
- over to the index because we didn't
- include index equals false
- so if we include both of those then we
- see the index is auto
- 0 1 the columns are auto 0 1 and we have
- those four values
- as cells of the data frame so which you
- pick in between these
- is just going to depend on how you have
- your data in excel if it has headers
- um then you would want to leave it as it
- is if it doesn't have headers you would
- want to pass header equals false
- and generally you're going to want to
- pass index equals false unless you want
- to have
- your left most values as the mx of the
- data frame and then
- you can exclude that
- so um then we can look at writing
- multiple values
- so um here if we just take a single
- value and we assign it
- across a range here d1 to d2 we're going
- to see that that same value
- comes into each one of the cells in that
- range
- um and then uh
- so a5 to b5 a5 to b5 that's here
- we want to put the 10 and 11 to there
- so same size range one dimensional
- it's going to be able to put those
- values there
- and then if you did a vertical range uh
- that would work as well so we can go a7
- to a8
- and that should uh oh that does
- it does always go horizontally you
- cannot just write vertically like that
- um if you want to write vertically
- what you have to do is go to this list
- of lists
- pattern and put each of these values
- in lists so then
- we can see that the values come
- vertically um
- and as a
- one-liner for that uh i'm just double
- checking we don't have that later in
- here
- nope so one liner for that if you
- already have your 10 and 11 lists
- and you want to put it vertically
- starting at
- a10 so
- my list is 10 12.
- then you can use a list comprehension to
- convert it into that structure
- um so you're just taking each element of
- the list and wrapping it
- into a list and then we should see from
- a 10 down to a11 that these numbers
- then come there so you can use this
- pattern
- if you want to output vertically
- um and then to output to an entire
- two two-dimensional range then we do
- this list of list patterns so here
- going from a7 to b8
- and you'll notice also that whatever is
- there it's going to overwrite it
- as well so now we have
- this 12 13 14 15 12 and 13 in the first
- row 14 and 15 in the second row
- in that a7 and b8 range
- so then um we can read entire
- rows or columns
- using the expand functionality in excel
- wings
- so expand it can automat if you don't
- pass anything it automatically goes
- down and right but you can also pass
- right or down to just go in that
- direction
- um so starting from this a7 if we expand
- to the right
- then we're going to get the 12 and the
- 13 because it started from here and
- expanded right to get both of those
- but if we target the same cell but
- expand down
- then we're going to get 12 and 14
- because it's going to start from here
- and expand down
- and if you don't pass anything into
- expand then it's going to get all of
- this
- with the list of lists kind of data
- structure
- but you can combine expand with options
- um
- and that lets us put this
- two-dimensional data into a data frame
- so using the same
- stuff we learned from the above section
- we're making that a data frame
- excluding the index excluding the header
- and we can see that we get
- the 12 13 14 15 with the auto
- incrementing
- columns and
- index
- so we can take an entire table from
- python we can take a python data frame
- um and we can write that into
- excel as well it just directly works
- uh here i already have something in a10
- i didn't need to
- delete it out but just so it's clear
- i'm going to write into a10 there with
- data frame
- and we can see that all of that comes in
- there
- now you will notice you know just like
- when we're
- reading into a data frame from excel
- that there are options around
- the index and the header
- because by default it's going to put the
- index
- and the columns there so we said start
- from a 10 we want to output this
- 16 17 18 19 but it also took this
- 0 and 1 uh index and column names and
- brought them in here as well
- um so now we're going to try here at 8
- 14
- and if we exclude the index and the
- header assign that data frame again
- now it's just going to take those values
- from the data frame
- rather than including the um
- index and column names
- so um there's an important
- gotcha when you want to work with
- integers
- uh and pulling them from excel into
- python
- and most of the time in python we don't
- really care whether it's an integer
- or a floating point number but
- in certain cases such as when we want to
- do a number of
- loops we do care whether it's
- an integer or a floating point number
- so um we're gonna pull in
- from f5 so f5 is this five here
- and we're going to um pull in
- the value from there as our number of
- loops variable
- and you can see it's just a plain five
- there's no decimals on it or anything
- here
- but we can see when it came into uh
- python that we got 5.0
- and we look at the type it is indeed a
- float not an integer
- and so that means if you just take that
- and you just try and loop with it
- then we're going to get this error float
- object cannot be interpreted as an
- integer
- um so what we have to do is convert that
- into an integer before we can loop over
- it
- so we just wrap that num loops in and in
- and then now it's an integer and so
- we can do our loops and everything on it
- um there are also some gotchas
- around formatting in excel when you try
- and read that back into python
- um so here let's look at the f1 value
- it's formatted as currency
- in excel let's pull that into python
- you can see it comes in as decimal 10.25
- and if you just try and add a number
- to this decimal we're going to get
- unsupported operand type
- decimal and float so what we can do
- same thing as we did with the integers
- you just convert it
- so just convert that to a float and then
- we can do the math with it just fine
- um and then
- f3 there we have the accounting
- formatting
- it's going to be the same thing but if
- you just take the float of that
- you're going to be able to get the
- number back from the decimal
- um and
- percentages do thankfully just work out
- of the box just fine
- it pulls it in as a float in the correct
- format
- and then if you have a date it's going
- to pull it
- as the date time type in python so
- that's a built-in
- type that we haven't talked about in the
- course
- but this is a standard way to work with
- dates in python
- and it works perfectly fine with pandas
- as well
- so the last thing that we're going to
- look at here um
- is recalculating the workbook
- so typically you're not going to need to
- do this
- because when you
- write an input into excel it's going to
- recalculate
- the workbook automatically
- so let me just write a value here
- um a2 let's make that 11.
- you can see that the random value over
- here recalculated
- when i did that i'm just running this
- python code over here
- and you can see the excel workbook
- recalculating over there
- so anytime that you write something into
- excel
- it's going to recalculate um and so you
- don't normally need to explicitly
- recalculate
- um the only time where you're going to
- need to do that
- is if you want to keep re-running the
- excel model
- when you're not changing any inputs and
- the case where that
- generally applies is when you have
- internal randomness
- in your excel model such that each time
- you run the excel model with the same
- inputs
- you're getting a different output each
- time so in that case
- you might want to have your python code
- do a loop
- where it uh you know recalculates the
- workbook and then extracts the output
- from excel
- and saves that in the list and then
- recalculates the workbook extracts it
- from excel saves it in the list and does
- that for some number of iterations
- so how can we do this recalculation so
- we do have to work with the app object
- instead of the book or sheet object um
- so this one liner here can get us access
- to the current
- uh application object excel app
- and then on that you can do app.api
- dot recalculate full and you notice when
- i run that that the random value in the
- excel sheet
- keeps recalculating
- um and
- you might try this on your machine and
- find it doesn't work
- and that's because the api is actually
- slightly different
- on x on
- mac versus windows so this command
- should work on mac while this one works
- on windows
- so in case you need to do this in your
- model
- i included a convenience function for
- you
- uh recalculate workbook you can just
- copy this function
- into your model um and you just call
- recalculate workbook
- and you see i call that it is
- recalculating and what this does is that
- it gets the app
- excel wings app um and then it's going
- to check
- what operating system you're on if
- you're on windows it's going to call
- that
- first calculate full if you're on mac
- it's going to call that just calculate
- and otherwise it's it's going to raise
- an error if it can't properly detect
- your operating system
- um so you can just take this into your
- model and use it should you need to
- recalculate
- uh your model because it's an internal
- randomness excel model that you're
- trying to run
- repeatedly from python
- that covers how to integrate uh python
- and excel using excel wings
- and there is a lab exercise multiple lab
- exercises on this material
- as well so
- there are actually four lab exercises
- here the different levels
- um and the fourth one is
- optional one two and three are what's
- required to submit for the course
- and number one here and with all the
- exercises
- you're going to download this uh excel
- wings lab file from the course site or
- from the resources slide here this
- directory will let you download it
- and the first exercise is to read
- the values in the workbook or the
- worksheet from column a
- and then write them into column b using
- python and excel wings the second
- exercise is to get the value
- from c9 and multiply it by 2.5
- in python and show the result and the
- third exercise
- is to read this table in a python do
- some math on it
- and output it back into excel and
- make sure that you can uh match up the
- index and header to the same format as
- the original
- table so that covers
- uh everything we're going to look at in
- the course with combining excel
- and python and this also concludes
- all the material um that you would need
- that everyone should need for every
- possible variation of the project 2
- model so thanks for listening
- and see you next time