Stuck waiting on Microsoft to grant you access to Python in Excel? Don't wait! Check out my video and start building skills NOW: ruclips.net/video/0iV4FtpSriY/видео.html
Well spoken David. I grew up with Excel’s own automation language in the 90s - it could do a lot but was a pain to use. I was pleased when Excel VBA came in as I had some familiarity with it in MS Access. I have always maintained that a major problem “power” Excel users have is trying to use Excel as a database application. Although it has improved over the years it still does very little on its own to enforce data and calculation integrity as many companies and individuals (including my late brother) found out to their chagrin and $ loss.
I am stopping half way through your video to tell you that the way you communicate this information in a conversational manner is PURE GOLD. Now onto the rest of this video and others that you have produced 😇
TBH, you shouldn't use Excel as a database! That was declared years ago by the VBA project manager, Joel Spolsky. (VBA was introduced to Excel - before being migrated to other Office apps - in 1993. Blimey, that dates me!) Nothing stopping you using Excel as a front end to large data. It's only worthwhile to use Python if you've got a large volume of data and that should naturally reside in separate DBMS, so you would be writing dedicated Python apps against THAT data. Sounds like a 'Me too' token gesture. Anyone done a latency test against, say, the max rowcount of a wide worksheet uploaded to Python?
Thanks for the comment! I would humbly offer the following for consideration: 1 - I 100% agree that Microsoft Excel shouldn't be used as a database. However, I've been in tech for a long time and know that Excel will always be used in suboptimal ways. It's not that Excel is an inherently bad tool. It's just that Excel is very flexible and ubiquitous, making it ripe to be the proverbial hammer in search of a nail. 2 - Performing advanced analytics on "Excel-sized" data is nothing new. For example, the books of Dr. Wayne Winston has taught Excel users for years how to conduct logistic regression and market basket analyses using Solver. Python in Excel is arguably a logical addition to Excel to allow users a better alternative to hand-rolling analyses using Solver. Check out my RUclips video comparing Solver to Python in Excel for logistic regression: ruclips.net/video/ekT4Dx0D0qY/видео.html 3 - While it doesn't use a wide dataset, I have a RUclips video showing the performance of Python in Excel using 330,000 rows of data that might interest you: ruclips.net/video/0ICD9zMMzZ4/видео.html
When Python for Excel came out, I looked into it and just didnt see the use of it. I use Python for Data Analytics using the pandas library and tend to only use Excel just for data output for people who would never understand any programming, they just want to see the end result. So I fail to see Python for Excel as a use to anyone, Especially if for example you extract data from SQL, are you wanting to upload your servers credentials to Microsoft ??? If you can use Python your just use Python otherwise use power query within Excel, Python for Excel just doesnt fit in any situation.
If I may be so bold, I would offer the following for consideration: 1 - Python in Excel uses a Power Query connection as an external data source. The authentication to the data source happens locally. 2 - Excel users have been conducting advanced analytics for many years. For example, using the Solver to implement logistic regression. Python in Excel offers a better way for these users to conduct data analyses. I have a video comparing Solver vs statsmodels for linear regression: ruclips.net/video/ekT4Dx0D0qY/видео.html 3 - Python in Excel offers a very low barrier to entry for Excel users. Regarding #3, I have clients that are highly interested in Python in Excel due to their IT departments preventing local Python installations. However, these clients are already using Azure and they see this as very easy way to unlock advanced analytics in their work. It is worthy to note that these clients are not IT folks (e.g., developers), but are data-savvy business professionals.
As I'm not familiar with AP/AR tasks, I wouldn't be able to comment. That being said, I know that Finance professionals often use VBA to help automated their Excel-centric processes.
Python in Excel as implemented by Microsoft does not satisfy data privacy requirements in Europe as required by GDPR regulations. As shown in this video, Microsoft only promises "not to persist data in the Microsoft Cloud". That does not preclude MS from copying your data or metadata to one of their non-cloud servers, etc. Not surprising, MS has a reputation for dragging its feet when it comes to complying with (data) privacy regulations instead of toeing the line as they should.
@@DaveOnData Meanwhile All of us could store/share our most valuable resource, private and/or company data stored in our Excel workbooks and databases over Power Query connections with Microsoft over Microsoft Cloud.
@@DaveOnData Depends on the exact use and the nature of data stored in the external cloud on the one hand, and how well the cloud service provider complies with GDPR regulations on the other hand.
@@DaveOnData Sometimes some companies intentionaly for some of their data use local/private datastores like local databases and/or local excels and/or send part of their data/knowledge in form of excels exclusivly to well known parties.
Nice! I'd never heard of Python In Excel and you pretty well told me what I need to know. I've written some VBA for Excel and for me it was all about writing scripts that automated long sequences of steps I'd been doing by hand and finding tedious. It's an annoying programming language if you come from any of the "real" programming languages but usable if you need it.
Best to use Python to externally perform data analysis and to then create spreadsheets. From within python, you can run macros from within Excel. You can also pass formulas to Excel as well. VBA is best used when you are already in Excel and want to run automation.
Many professionals don't have the option of running a local Python install. Python in Excel can be an easy way for these professionals to unlock advanced analytics.
Dave your T-shirts are great - I also love Iron Maiden….but recently listing Saxon.. My experience with vba is, that this is great tool for connecting with data bases and providing data from there. Ofcourse with little help of SQL. Now PQ cope with that but pure sql is more efficient (you can adjust your sql query using date, or other parameters from spreadsheet - in PQ is more difficult cause of safety issues). Regards and waiting for a new episode 😊
Saxon! I haven't listened to that band in a very long time. I had a copy of "Crusader" on tape in the 1980s. 😁 My next video, out next Wednesday, will explore the tradeoffs between pure Power Query and pure SQL for Python in Excel.
Learn VBA! This language has been developed specifically for Office application development, while Python has only recently been "forced" into Office because this seems to be a new advertising strategy of the Microsoft masterminds. Python is not more powerful than VBA in anything, apart from the function libraries (written in other languages) that Python accesses; if NumPy or Panda were made directly available for Office, there would simply be no reason to use Python in Office. Learning VBA is also a good idea because VB (even if no longer developed by MS) is an excellent bridge language to Xojo, B4X and Mercury - an investment in the future.
It is worthless trying to use Python in my company as they would not allow IT any IDE to be installed due to compliance reasons (they block every, everything). So I got into VBA long time ago.
This is precisely why many of my clients are excited by Python in Excel - no local installation is required. Additionally, many of their IT departments are already using Azure, making Python in Excel a very attractive option for them.
Most important I learned where Python runs! What was missing is that VBA is a cross Office tool. For example I wrote a tool which read email then worked on the excel attachment and then sent out emails. You can also access word and powerpoint with VBA.
Excellent. Thank you for the explanation. I have one more question. Is it possible to insert or execute Python formulas in a cell using VBA? If this is possible, it seems like additional automation could be achieved.
I checked with my contact at Microsoft and here's the response: Yup, you just write out the formula using range.formula = “=PY(, )” In beta, you currently have to have users click the try preview first otherwise it might throw an exception
Great video, I have just subscribed your channel. Thank you for expanding the differences between Python run in Excel and on PC. I wanted to use Python on my company PC for some automations, however I don't feel comfortable with it having access to everything (as a beginner I might make some mistakes resulting in unexpected outcome). Therefore I will limit its use to safe boundaries within Excel and try to look for automations in Power Automate/Apps (not big fan of VBA).
Python for the data analysis, VBA for controlling getting input data in environments that don't connect Data Verse or other outside data sources. (Think ETL actions across SharePoint/OneDrive locations.) _WS
@@DaveOnData Sure, but the issue is not a lot of businesses open up DataVerse or DataLake to operational level psudo-developers so you're stuck back into the VBA work-around for automation as the odds are power automate isn't connected either and you will not have Pro license.
Hi David, thank you for this. I have a different question for you. Between using Python in Excel and Power Query, which one would you recommend? If possible, would you create a video explaining the differences and benefits of each option? Thank you in advance.
This is my opinion. Please take it as one perspective among many. I'm going to make an assumption that Microsoft Excel is your data analysis tool of choice. As a novice Excel Data Analyst, it is unlikely that you will need VBA anytime soon - maybe never. Python in Excel is really for more advanced analytics, so I would first focus on the fundamentals using out-of-the-box features. For example, my exploratory data analysis (EDA) with Excel tutorial series: ruclips.net/p/PLTJTBoU5HOCRFQhfU1gg2ciNpS_evWKR7
This is a complaint I frequently hear, and I understand it. There are also many organizations invested in Azure and they see the ease of use as a big win over maintaining local Python installations.
@@DaveOnData yes - I totally understand this is for the Azure users. It basically integrates with Azure, so from that point of view it makes perfect sense. However most users won't use Azure. And since most users also won't be into Data Engineering/Analysis, it does not make sense for them. Still - the news titles that "Python is available in Excel" come misleading, as people understand that it would be available as VBA and would do similar things and they think they could use it to a degree. Also providing a full-blown Python in Excel would be a total security compromise, but hey isn't VBA a security risk too? I remember the 2000s era when you would get each week at least one email with attached word/excel file with some VBA malware. So what. I guess solution is to still - limit the Python, therefore maybe provide a Python fork, with MS Office-specific functions, but all of it to function locally. Then MS would get more people into MS Office customizations. If anyone really needs it. Maybe the Libre Office folks would get this idea faster than MS guys and provide this for us, as sometimes it would be significantly faster to provide a Spreadsheet file + attached script for a task, instead to code a GUI from scratch and provide custom functionality... No idea how many would need it, but guess there is a market for such things.
I would offer this for consideration. In my experience, most Excel users do not immediately associate Python in Excel with VBA/Excel automation. In fact, my experience has been most Excel users don't really know what the point of Python in Excel is. 🤣 The best way I've found to explain Python in Excel is that it allows for advanced analytics that used to be hand-rolled using Solver or just weren't possible unless you purchased an Excel Add-in.
Hello! Could Excel 2023 with the addition of Python and Copilot recognize the data type and approximate it correctly, and then find the transition points from one type of pattern to another?
Deep neural networks can learn very complex relationships from data. The trick is that they need a lot of examples from which to learn. So it is possible in theory.
But… I thought I had recently read that Microsoft is no longer supporting VBA. And are there not existing Python libraries that can “read” or “write to” Excel files? (Kinda a noob here, but wanting to add to my skill set and seeing how Python might help - my college “programming” was in MATLAB and Fortran77 (digital compiler), and I took a bit of Python3 during the early months of the pandemic. I essentially work as a mechanical product engineer.)
Great questions! First, regardless of Microsoft trying to migrate folks off VBA (e.g., to Office Script), there is a huge installed base of VBA code and VBA developers. It's not going anywhere anytime soon. Second, programming languages like R and Python have libraries for reading in, manipulating, and writing out Microsoft Excel files. However, this isn't always an option for many Excel users for two primary reasons: 1- They don't want to install and maintain a programming language on their laptop. Python, in particular, is a pain in this regard. 2- If they wanted to do this, they couldn't because of restrictions put in place by their IT department. Many of my clients are excited by Python in Excel because it provides a quick and easy path to advanced analytics within Excel.
@jasongins - In general, I'm a big fan of Anaconda. For example, I use Anaconda in my Python courses at TDWI conferences. That being said, I'm not super familiar with Anaconda Cloud. If I put on my former Enterprise Architect hat, the things I would be curious about the costs and security.
It does, indeed! Although "fast" is a relative term. You may find my video interesting. It demonstrates importing 330,000 rows of data into Python in Excel: ruclips.net/video/0ICD9zMMzZ4/видео.html
Office Scripts is where Microsoft wants people to go. That being said, there is a large installed base of VBA code/users. In my experience, VBA isn't going anywhere anytime soon.
It's a glorified version of the code recorder, which has been in Office products for years. The recording facility was removed from Powerpoint some years ago because processing the actions became too difficult to code! So maybe that's MS' thinking about future Office products. Nothing you can't do in VBA - even making calls to the .Net and Windows libraries if you need lower level control.
I respectfully disagree with this. Python in Excel does not compete with VBA or Office Scripts in any way. It is designed specifically to enable data analyses that are difficult or impossible to do with out-of-the-box Excel. For example, you could code up a random forest algorithm in VBA, but why would you do that when Python in Excel gives you access with just a few lines of code? Also, Python in Excel addresses a huge shortcoming in doing analytics with Excel - having to hand-roll worksheet templates using Solver. I have a video that discusses this: ruclips.net/video/ekT4Dx0D0qY/видео.html
Python in Excel is more like Excel in Python. Which, of course, really doesn't work. Good advanced geeky stuff, but useless in 99% of most Excel environments. As David does acknowledge. If you're serious about Excel, don't waste time on Python. For advanced Excel, that really works, Power Query is the way to go! Otherwise, you are coding Python, not Excel...
I'd like to offer this up for consideration. For years, Dr. Wayne Winston has authored books on performing advanced analytics in Excel (e.g., using the Solver). Python in Excel is a much better way to do these things.
That is always a consideration with Excel - make sure it will scale to the size of your data. One great thing about Python in Excel is that it provides a smooth path to scale. For example, moving to Jupyter Notebooks if needed.
the thing is that microsoft does NOT control python. imagine large companies starts using it and in 10 years there is ok we have python 4 or something which is NOT compatible with python 2 (like python 2-3) for me i’m ok but imagine billion dollars banks…. microsoft must control the programming language -> office script but well it’s fat far far behind VBA honestly. with vba alone you can do many things but office script you may need to use power automate aswell which makes everything a big mess
Ah, C++ is my favorite software engineering language! To be honest, as much as I love C++, the speed of languages like R and Python really doesn't matter for analytics.
Wow! Thank you for taking the time to write these words - they are much appreciated. As I will discuss in my next video, I'm a huge fan of PQ in the right situations. In others, technologies like SQL and Python in Excel are the ticket.
@@DaveOnData, for me PQ has one advantage of being available going back to Excel 2010. Also a lot of hospitals don't allow Macros. But following you for Python and dipping my toes further into SQL. One of the hospitals here in Chicago I work the most with has Office 2016, very limited access to SQL and PBI. They allow one Director one folder so I can do somethings in VBA for her. But I agree with you.
VBA is also used for interactive controls, for instance form buttons to add an entry to a worksheet. Unfortunately, Microsoft does not support VBA for iOS devices, which means a lot of Office automation is not available on that platform. iPhones and iPads have grown in the business world. So people are starting to abandon Microsoft as their goto one-stop-shop.
Interesting perspective! Personally, I'm not seeing this as all of my clients are Microsoft shops. In particular, my SMB clients are heavy Microsoft Excel users on Windows.
Generally speaking, Python is the new javascript. A scripting language with strong ambitions to become a programming language. As a professional developer, I’m not interested, and this is what I tell RUclips everytime it suggests a video about Python: “not interested”. But it keeps on suggesting… there must be a bug. Perhaps RUclips is written in Python. 😂
Such a refreshing take on the popularity of Python! What's your favorite language for software engineering? Mine is C++. Unfortunately, I haven't needed to use it for many years.
Python IS a programming language. Much easier than Java, sometimes at the same speed of Java, but with a huge number of libraries and framework for web apps, data science, numerical anaylisis, simulation and so on and so forth.
@@DaveOnData Most of my customers tell me what language I must use. Just in case I might retire or die, I suppose. So I actually use a dozen of languages, including Python. My favorite languages are those that you can rely upon when developing big projects, so they must provide type-safety and null-safety, as is the case with C# or Dart, but also C++ or Java; which one I prefer in a specific case depends on the application. As far as Excel is concerned, sure Python would be better than VBA. But if the code size increases too much, perhaps Excel itself is not the right tool. Thanks for the video, I hope my first comment didn’t seem impolite.
Sir you don't have any idea, what are you talking about 😅 Python was created in 1991 for scripting at a small startup by Guido ( now works at Microsoft and helped in developing the python for excel ), python is a full fledged programming language
Stuck waiting on Microsoft to grant you access to Python in Excel? Don't wait! Check out my video and start building skills NOW: ruclips.net/video/0iV4FtpSriY/видео.html
I like this video ❤❤❤
Well spoken David. I grew up with Excel’s own automation language in the 90s - it could do a lot but was a pain to use. I was pleased when Excel VBA came in as I had some familiarity with it in MS Access. I have always maintained that a major problem “power” Excel users have is trying to use Excel as a database application. Although it has improved over the years it still does very little on its own to enforce data and calculation integrity as many companies and individuals (including my late brother) found out to their chagrin and $ loss.
Thank you for taking the time to share your experiences. Any thoughts on Power Pivot as a database substitute?
I am stopping half way through your video to tell you that the way you communicate this information in a conversational manner is PURE GOLD. Now onto the rest of this video and others that you have produced 😇
Wow! Thank you so much for the kind words. I am happy to hear that you are enjoying the content.
TBH, you shouldn't use Excel as a database! That was declared years ago by the VBA project manager, Joel Spolsky. (VBA was introduced to Excel - before being migrated to other Office apps - in 1993. Blimey, that dates me!) Nothing stopping you using Excel as a front end to large data. It's only worthwhile to use Python if you've got a large volume of data and that should naturally reside in separate DBMS, so you would be writing dedicated Python apps against THAT data. Sounds like a 'Me too' token gesture. Anyone done a latency test against, say, the max rowcount of a wide worksheet uploaded to Python?
Thanks for the comment! I would humbly offer the following for consideration:
1 - I 100% agree that Microsoft Excel shouldn't be used as a database. However, I've been in tech for a long time and know that Excel will always be used in suboptimal ways.
It's not that Excel is an inherently bad tool. It's just that Excel is very flexible and ubiquitous, making it ripe to be the proverbial hammer in search of a nail.
2 - Performing advanced analytics on "Excel-sized" data is nothing new. For example, the books of Dr. Wayne Winston has taught Excel users for years how to conduct logistic regression and market basket analyses using Solver.
Python in Excel is arguably a logical addition to Excel to allow users a better alternative to hand-rolling analyses using Solver. Check out my RUclips video comparing Solver to Python in Excel for logistic regression: ruclips.net/video/ekT4Dx0D0qY/видео.html
3 - While it doesn't use a wide dataset, I have a RUclips video showing the performance of Python in Excel using 330,000 rows of data that might interest you: ruclips.net/video/0ICD9zMMzZ4/видео.html
When Python for Excel came out, I looked into it and just didnt see the use of it. I use Python for Data Analytics using the pandas library and tend to only use Excel just for data output for people who would never understand any programming, they just want to see the end result.
So I fail to see Python for Excel as a use to anyone, Especially if for example you extract data from SQL, are you wanting to upload your servers credentials to Microsoft ???
If you can use Python your just use Python otherwise use power query within Excel, Python for Excel just doesnt fit in any situation.
If I may be so bold, I would offer the following for consideration:
1 - Python in Excel uses a Power Query connection as an external data source. The authentication to the data source happens locally.
2 - Excel users have been conducting advanced analytics for many years. For example, using the Solver to implement logistic regression. Python in Excel offers a better way for these users to conduct data analyses. I have a video comparing Solver vs statsmodels for linear regression: ruclips.net/video/ekT4Dx0D0qY/видео.html
3 - Python in Excel offers a very low barrier to entry for Excel users.
Regarding #3, I have clients that are highly interested in Python in Excel due to their IT departments preventing local Python installations. However, these clients are already using Azure and they see this as very easy way to unlock advanced analytics in their work. It is worthy to note that these clients are not IT folks (e.g., developers), but are data-savvy business professionals.
Thanks David. Been wondering about this question for a while. I now know the answer.
My pleasure! I hope you found the content useful.
Sounds cool, thanks. How could this help with ap/ar tasks automation?
As I'm not familiar with AP/AR tasks, I wouldn't be able to comment. That being said, I know that Finance professionals often use VBA to help automated their Excel-centric processes.
Python in Excel as implemented by Microsoft does not satisfy data privacy requirements in Europe as required by GDPR regulations. As shown in this video, Microsoft only promises "not to persist data in the Microsoft Cloud". That does not preclude MS from copying your data or metadata to one of their non-cloud servers, etc. Not surprising, MS has a reputation for dragging its feet when it comes to complying with (data) privacy regulations instead of toeing the line as they should.
A fair criticism! We shall see how Microsoft will address (or not) what you mention here.
@@DaveOnData Meanwhile All of us could store/share our most valuable resource, private and/or company data stored in our Excel workbooks and databases over Power Query connections with Microsoft over Microsoft Cloud.
@RedShiftGalaxy - Do you mean to say that organizations should never use cloud providers?
@@DaveOnData Depends on the exact use and the nature of data stored in the external cloud on the one hand, and how well the cloud service provider complies with GDPR regulations on the other hand.
@@DaveOnData Sometimes some companies intentionaly for some of their data use local/private datastores like local databases and/or local excels and/or send part of their data/knowledge in form of excels exclusivly to well known parties.
Walker Laura Jones Margaret Anderson Patricia
Thank you for suggesting the video! Much appreciated.
Nice! I'd never heard of Python In Excel and you pretty well told me what I need to know. I've written some VBA for Excel and for me it was all about writing scripts that automated long sequences of steps I'd been doing by hand and finding tedious. It's an annoying programming language if you come from any of the "real" programming languages but usable if you need it.
Excellent! Glad that you found the video's content useful.
Best to use Python to externally perform data analysis and to then create spreadsheets. From within python, you can run macros from within Excel. You can also pass formulas to Excel as well. VBA is best used when you are already in Excel and want to run automation.
Many professionals don't have the option of running a local Python install. Python in Excel can be an easy way for these professionals to unlock advanced analytics.
Very good.
Your "700 million users..
" etc etc., sum the question in a nutshell.
Cheers and thanks
Thank you! I am glad you enjoyed the video.
Teach me everything you know.
I've got many tutorials on my RUclips channel. Check them out!
I can think of nothing more annoying than having a msgbox that pops up telling me to rename every new sheet
Agreed! However, the annoyance aligns with the hypothetical example.
@@DaveOnData true. The video was great!
so... the proper question is...
Office Script vs VBA
M Code vs Python Excel
DAX vs Python Excel
Agreed! I would also add (which will be the subject of my next video):
SQL vs Power Query/M
Oh, and here are a couple more:
Solver vs Python in Excel
Analysis ToolPak vs Python in Excel
Dave your T-shirts are great - I also love Iron Maiden….but recently listing Saxon.. My experience with vba is, that this is great tool for connecting with data bases and providing data from there. Ofcourse with little help of SQL. Now PQ cope with that but pure sql is more efficient (you can adjust your sql query using date, or other parameters from spreadsheet - in PQ is more difficult cause of safety issues). Regards and waiting for a new episode 😊
Saxon! I haven't listened to that band in a very long time. I had a copy of "Crusader" on tape in the 1980s. 😁
My next video, out next Wednesday, will explore the tradeoffs between pure Power Query and pure SQL for Python in Excel.
@@DaveOnData Interesting video considering MS access comes with a 365 subscription and easily interfacing with each other.
Learn VBA! This language has been developed specifically for Office application development, while Python has only recently been "forced" into Office because this seems to be a new advertising strategy of the Microsoft masterminds. Python is not more powerful than VBA in anything, apart from the function libraries (written in other languages) that Python accesses; if NumPy or Panda were made directly available for Office, there would simply be no reason to use Python in Office. Learning VBA is also a good idea because VB (even if no longer developed by MS) is an excellent bridge language to Xojo, B4X and Mercury - an investment in the future.
Thank you for offering your perspective! What do you think about Python in Excel for helping users perform analytics?
NumPy and Panda are Python, you can't say there is no reason to use Python in Office xD
Yea it’s a lot easier to access API data from Python than VBA
It is worthless trying to use Python in my company as they would not allow IT any IDE to be installed due to compliance reasons (they block every, everything). So I got into VBA long time ago.
This is precisely why many of my clients are excited by Python in Excel - no local installation is required. Additionally, many of their IT departments are already using Azure, making Python in Excel a very attractive option for them.
Excellent! Very clearly explained. Thanks
Thank you! I appreciate the kind words and I am glad you found the video useful.
Ok...this is the first video on python that made any sense to me! Well done on your style of teaching.
Thank you for the comment! I'm glad you found the video useful.
Most important I learned where Python runs! What was missing is that VBA is a cross Office tool. For example I wrote a tool which read email then worked on the excel attachment and then sent out emails. You can also access word and powerpoint with VBA.
Glad you found some aspect of the video useful!
Excellent. Thank you for the explanation. I have one more question. Is it possible to insert or execute Python formulas in a cell using VBA? If this is possible, it seems like additional automation could be achieved.
I checked with my contact at Microsoft and here's the response:
Yup, you just write out the formula using range.formula = “=PY(, )”
In beta, you currently have to have users click the try preview first otherwise it might throw an exception
Great video, I have just subscribed your channel. Thank you for expanding the differences between Python run in Excel and on PC. I wanted to use Python on my company PC for some automations, however I don't feel comfortable with it having access to everything (as a beginner I might make some mistakes resulting in unexpected outcome). Therefore I will limit its use to safe boundaries within Excel and try to look for automations in Power Automate/Apps (not big fan of VBA).
@tomoleusz - Thank you for taking the time to leave a comment and I am glad to hear you have found my content useful!
wishing for local python. MS is getting access to all that yummy scripts that users develop for them.
Arguably, they've been getting that for years now with Azure. 🤣
Python for the data analysis, VBA for controlling getting input data in environments that don't connect Data Verse or other outside data sources. (Think ETL actions across SharePoint/OneDrive locations.) _WS
@whosestone - What are your thoughts on Power Query for ETL?
@@DaveOnData Sure, but the issue is not a lot of businesses open up DataVerse or DataLake to operational level psudo-developers so you're stuck back into the VBA work-around for automation as the odds are power automate isn't connected either and you will not have Pro license.
Hi David, thank you for this. I have a different question for you. Between using Python in Excel and Power Query, which one would you recommend? If possible, would you create a video explaining the differences and benefits of each option? Thank you in advance.
@mcwahaab - I cover your question in this video: ruclips.net/video/rMjtcsmHf9g/видео.html
@@DaveOnData - Thanks a lot. Appreciate
@Daveondata for a novice data analyst, do you recommend learning VBA or Python+Excel given the other tools available?
This is my opinion. Please take it as one perspective among many.
I'm going to make an assumption that Microsoft Excel is your data analysis tool of choice. As a novice Excel Data Analyst, it is unlikely that you will need VBA anytime soon - maybe never. Python in Excel is really for more advanced analytics, so I would first focus on the fundamentals using out-of-the-box features. For example, my exploratory data analysis (EDA) with Excel tutorial series: ruclips.net/p/PLTJTBoU5HOCRFQhfU1gg2ciNpS_evWKR7
Thanks David
You are welcome! I hope you found the video useful.
Can't wait for more. Excellent again, so glad I found you 😀
Thank you!
Great video. Very informative to me with no coding/programming experience.
@KeithJBrett - Thank you for this feedback! I'm glad you found the video useful.
Thank you for the explanation. I don't like they that the data flies to the cloud. It should stay local. Anyway. I'm not gonna use it in Excel.
This is a complaint I frequently hear, and I understand it. There are also many organizations invested in Azure and they see the ease of use as a big win over maintaining local Python installations.
@@DaveOnData yes - I totally understand this is for the Azure users. It basically integrates with Azure, so from that point of view it makes perfect sense. However most users won't use Azure. And since most users also won't be into Data Engineering/Analysis, it does not make sense for them. Still - the news titles that "Python is available in Excel" come misleading, as people understand that it would be available as VBA and would do similar things and they think they could use it to a degree. Also providing a full-blown Python in Excel would be a total security compromise, but hey isn't VBA a security risk too? I remember the 2000s era when you would get each week at least one email with attached word/excel file with some VBA malware. So what. I guess solution is to still - limit the Python, therefore maybe provide a Python fork, with MS Office-specific functions, but all of it to function locally. Then MS would get more people into MS Office customizations. If anyone really needs it. Maybe the Libre Office folks would get this idea faster than MS guys and provide this for us, as sometimes it would be significantly faster to provide a Spreadsheet file + attached script for a task, instead to code a GUI from scratch and provide custom functionality... No idea how many would need it, but guess there is a market for such things.
I would offer this for consideration. In my experience, most Excel users do not immediately associate Python in Excel with VBA/Excel automation. In fact, my experience has been most Excel users don't really know what the point of Python in Excel is. 🤣
The best way I've found to explain Python in Excel is that it allows for advanced analytics that used to be hand-rolled using Solver or just weren't possible unless you purchased an Excel Add-in.
just use python since you can always do more if you have to
This can be problematic for many professionals. For example, many cannot install Python locally due to IT restrictions.
Hello! Could Excel 2023 with the addition of Python and Copilot recognize the data type and approximate it correctly, and then find the transition points from one type of pattern to another?
Based on my experience, this is possible. The caveat would be that crafting the right prompt(s) can be tricky depending on the data.
@@DaveOnData I have several formulas that I use, can neural network combine them into one and output them as one formula?
Deep neural networks can learn very complex relationships from data. The trick is that they need a lot of examples from which to learn.
So it is possible in theory.
But… I thought I had recently read that Microsoft is no longer supporting VBA. And are there not existing Python libraries that can “read” or “write to” Excel files? (Kinda a noob here, but wanting to add to my skill set and seeing how Python might help - my college “programming” was in MATLAB and Fortran77 (digital compiler), and I took a bit of Python3 during the early months of the pandemic. I essentially work as a mechanical product engineer.)
Great questions!
First, regardless of Microsoft trying to migrate folks off VBA (e.g., to Office Script), there is a huge installed base of VBA code and VBA developers. It's not going anywhere anytime soon.
Second, programming languages like R and Python have libraries for reading in, manipulating, and writing out Microsoft Excel files. However, this isn't always an option for many Excel users for two primary reasons:
1- They don't want to install and maintain a programming language on their laptop. Python, in particular, is a pain in this regard.
2- If they wanted to do this, they couldn't because of restrictions put in place by their IT department.
Many of my clients are excited by Python in Excel because it provides a quick and easy path to advanced analytics within Excel.
@DaveOnData what's your take on anaconda cloud notebook as an option for people with IT department restrictions on a local install?
@jasongins - In general, I'm a big fan of Anaconda. For example, I use Anaconda in my Python courses at TDWI conferences. That being said, I'm not super familiar with Anaconda Cloud. If I put on my former Enterprise Architect hat, the things I would be curious about the costs and security.
Any halfway decent language replaces VBA.
Any suggestions for automating within Excel that IT will commonly allow? Office Script?
Great video! Thank you! Subbed! :)
You are welcome! Glad you enjoyed the video.
Sir, 🙏🏼 i dont know much but i think python helps to process data very fast✨
It does, indeed! Although "fast" is a relative term. You may find my video interesting. It demonstrates importing 330,000 rows of data into Python in Excel: ruclips.net/video/0ICD9zMMzZ4/видео.html
@@DaveOnData ✨🙏🏼✨
Thank you Dave for the this great video with clear explanation. Love the shirt!
Glad you liked the video and the t-shirt! 😁
Excellent explanation. Thank you!
You are welcome! Glad you found the video useful.
What about Office Scripts? I think that’s the new VBA that Microsoft is pushing users towards.
Office Scripts is where Microsoft wants people to go. That being said, there is a large installed base of VBA code/users.
In my experience, VBA isn't going anywhere anytime soon.
@@DaveOnData Being just 365 hurts it as well.
It's a glorified version of the code recorder, which has been in Office products for years. The recording facility was removed from Powerpoint some years ago because processing the actions became too difficult to code! So maybe that's MS' thinking about future Office products. Nothing you can't do in VBA - even making calls to the .Net and Windows libraries if you need lower level control.
I respectfully disagree with this. Python in Excel does not compete with VBA or Office Scripts in any way. It is designed specifically to enable data analyses that are difficult or impossible to do with out-of-the-box Excel. For example, you could code up a random forest algorithm in VBA, but why would you do that when Python in Excel gives you access with just a few lines of code?
Also, Python in Excel addresses a huge shortcoming in doing analytics with Excel - having to hand-roll worksheet templates using Solver. I have a video that discusses this: ruclips.net/video/ekT4Dx0D0qY/видео.html
The answer is definitely C# 😁
Ha! Written a fair amount of C# code in my day. It's my 2nd favorite language for software engineering behind C++.
My 1st ver. was ver. 5 (1993) which included VBA for 1st time. It came on 11 floppy discs.
Yes! I remember the days when I would install Office using 30+ 3.5" floppy disks. 🤣
Python in Excel is more like Excel in Python. Which, of course, really doesn't work. Good advanced geeky stuff, but useless in 99% of most Excel environments. As David does acknowledge. If you're serious about Excel, don't waste time on Python. For advanced Excel, that really works, Power Query is the way to go! Otherwise, you are coding Python, not Excel...
I'd like to offer this up for consideration. For years, Dr. Wayne Winston has authored books on performing advanced analytics in Excel (e.g., using the Solver). Python in Excel is a much better way to do these things.
Thank you for that information- excellent!
You are welcome. I hope you find my future videos useful as well.
vba on large dataset is too slow
That is always a consideration with Excel - make sure it will scale to the size of your data.
One great thing about Python in Excel is that it provides a smooth path to scale.
For example, moving to Jupyter Notebooks if needed.
Python, VBA, M, SQL, Dax, even Excel worksheet functions - all great tools to me.
@rogeryang18 - Indeed! Python in Excel is another tool specifically designed for analytics.
the thing is that microsoft does NOT control python. imagine large companies starts using it and in 10 years there is ok we have python 4 or something which is NOT compatible with python 2 (like python 2-3)
for me i’m ok but imagine billion dollars banks….
microsoft must control the programming language -> office script
but well it’s fat far far behind VBA honestly. with vba alone you can do many things but office script you may need to use power automate aswell which makes everything a big mess
Wouldn't any organization that uses Python for data analysis (e.g., Jupyter Notebooks) face the same risk?
thanks very much!
You are welcome! Glad you enjoyed the video.
Very well explained.
Thank you for the feedback, it is greatly appreciated!
Thanks for the info
You are welcome! Unfortunately, Microsoft hasn't done a good job of making it clear how Python in Excel adds value to Microsoft Excel.
The python works on excel sheet, how to work
I'm not tracking your question. Can you elaborate?
Be hornest. Python performance is bad.
Compared to what? Performance is always relative
Compared to C++
Ah, C++ is my favorite software engineering language! To be honest, as much as I love C++, the speed of languages like R and Python really doesn't matter for analytics.
Nice insight. I'm heavily in the PQ house. But watching your videos on Python has opened up some possibilities in my little brain.
Thanks as always.
Wow! Thank you for taking the time to write these words - they are much appreciated. As I will discuss in my next video, I'm a huge fan of PQ in the right situations. In others, technologies like SQL and Python in Excel are the ticket.
@@DaveOnData, for me PQ has one advantage of being available going back to Excel 2010. Also a lot of hospitals don't allow Macros. But following you for Python and dipping my toes further into SQL.
One of the hospitals here in Chicago I work the most with has Office 2016, very limited access to SQL and PBI. They allow one Director one folder so I can do somethings in VBA for her. But I agree with you.
VBA is also used for interactive controls, for instance form buttons to add an entry to a worksheet.
Unfortunately, Microsoft does not support VBA for iOS devices, which means a lot of Office automation is not available on that platform.
iPhones and iPads have grown in the business world. So people are starting to abandon Microsoft as their goto one-stop-shop.
Interesting perspective! Personally, I'm not seeing this as all of my clients are Microsoft shops.
In particular, my SMB clients are heavy Microsoft Excel users on Windows.
Generally speaking, Python is the new javascript. A scripting language with strong ambitions to become a programming language. As a professional developer, I’m not interested, and this is what I tell RUclips everytime it suggests a video about Python: “not interested”. But it keeps on suggesting… there must be a bug. Perhaps RUclips is written in Python. 😂
Such a refreshing take on the popularity of Python! What's your favorite language for software engineering?
Mine is C++. Unfortunately, I haven't needed to use it for many years.
Python IS a programming language. Much easier than Java, sometimes at the same speed of Java, but with a huge number of libraries and framework for web apps, data science, numerical anaylisis, simulation and so on and so forth.
are you high?
@@DaveOnData Most of my customers tell me what language I must use. Just in case I might retire or die, I suppose. So I actually use a dozen of languages, including Python.
My favorite languages are those that you can rely upon when developing big projects, so they must provide type-safety and null-safety, as is the case with C# or Dart, but also C++ or Java; which one I prefer in a specific case depends on the application.
As far as Excel is concerned, sure Python would be better than VBA. But if the code size increases too much, perhaps Excel itself is not the right tool.
Thanks for the video, I hope my first comment didn’t seem impolite.
Sir you don't have any idea, what are you talking about 😅
Python was created in 1991 for scripting at a small startup by Guido ( now works at Microsoft and helped in developing the python for excel ), python is a full fledged programming language