Join 15,000 to Learn Advanced Data Analytics Skills. Through step by step lectures, complete with downloadable code examples and student support. R Tidyverse Reporting and Analytics for Excel Users Level up your advanced data analytic skills. Learn one of the fastest easiest ways to work with large datasets. www.udemy.com/course/r-tidyverse-reporting-and-analytics-for-excel-users/?referralCode=E6333F435381F1A83CCD R Shiny Flex Dashboards and Interactive Data Visualizations The fastest, easiest way to get up to speed with R Shiny to transform your analysis into data driven applications. www.udemy.com/course/r-shiny-flexdashboards/?referralCode=15405DCD94D9E80B5440 Easy Excel Dashboards, Models, Visualizations with Power Query Better than VBA. A no code way to easily transform data and build automated reports with Microsoft Excel www.udemy.com/course/easy-excel-dashboards-models-visualizations-power-query/?referralCode=331E9DFAE5A544402066 What do you want to know about advanced analytics and data science? Leave a comment below.
very nice and simple explanation. I whis my users understood this too, because they rely too much on excel and then complaint about the limitations of such format... (I'm learning R and it is amazing)
Thanks for your comment. Excel has it's place but if users are hitting limitations or going anywhere VBA then R will be amazing. If people work with data every day then R is a must.
Just a tip: Excel can handle more than 1M rows. It's just displaying that's impossible. Import it via powerquery, export via pivot table or alike to fit the format (
Regarding Point 1 and 2: Power Query, an Excel feature, can handle millions of rows easily. Also, it gives you a visual representation when dealing with data. Regarding Point 3: You don't need to code every step done by hand in VBA. This is a beginner's approach. Here is your code rewrited: -------------------------------------------------------------------------------------------------------------------------------- Sub AddNewColumn() With Range("J2:J" & Range("A1").CurrentRegion.Rows.Count) .Offset(-1).Cells(1) = "AnnualBase" .FormulaR1C1 = "=IF(RC[-4]=""HOURLY"",RC[-9]*40*52,RC[-9]*5*52)" .Value = .Value End With End Sub -------------------------------------------------------------------------------------------------------------------------------- Or using a variable, if you want to save an extra line: -------------------------------------------------------------------------------------------------------------------------------- Sub AddNewColumn() Set Rng = Range("J2:J" & Range("A1").CurrentRegion.Rows.Count) Rng.Offset(-1).Cells(1) = "AnnualBase" Rng.FormulaR1C1 = "=IF(RC[-4]=""HOURLY"",RC[-9]*40*52,RC[-9]*5*52)" Rng.Value = Rng.Value End Sub -------------------------------------------------------------------------------------------------------------------------------- Also, you can write functions and subs for every step when working with data, giving it meaningful names, and making it easier to test, mantain and scale. Regarding Point 4: Power View or better dashboards referencing a common source of data can do the job regarding showing it inside the company. Since almost all computers in most companies has Excel installed, this is not a problem. If you want to show data externally, I don't recommend Excel for it. A better option using Microsoft's products is Power BI, which is similar to Power Query, Power Pivot and Power View in Excel. Regarding Point 5: Excel, by itself, doesn't have ways to organize collaboration. In the same way, R, as a programming language, doesn't have either. We need other tools to do this job. P.S.: Git works with Excel files and VBA code. Excel is a good tool, but you should know how and when to use it. This applies to any other tool on the market. If you, reader, is considering R or Excel, you can learn both, since they have enough different use cases to be complementary.
Hi Daniel, thanks so much for your in-depth commentary. I agree R and Excel both have their use cases. This video is somewhat simplified to illustrate that there are other applications other than Excel that perform significantly better than the way that most people use Excel. Many people don't know anything other than Excel and naturally progress to recording VBA macros even though this is not the best way to do things. Just because it's still Excel doesn't make it easier. Thanks for taking the time to refactor the recorded VBA code. The majority of VBA users would not be able to do this which is why I decided to leave the longer code in. I also teach people power query on this channel as I think it's a great next step for a lot of people. I decided to leave it out of this video as it blurs message. Power Query is definitely easier and gets you closer to what you can do with a tool like R but R is so much more powerful, easier to automate, build proper applications, easier to explore stats, explore machine learning, give you greater career options. Many advanced Excel users will find R to be one of the best tools they never heard about. Power Query can only handle millions of records if it bypasses sheets and imports directly into the Power Pivot data model. VBA as far as I'm aware doesn't really work with Power Query or the Power Pivot Data Model. So if you stick with VBA you are stuck with the limits of cell based Excel which has a practical limit of tens of thousands of records and is extremely slow as it has to calculate each cell instead of entire vector (equivalent to an entire column). I would still use VBA for the occasional job like, sending emails via outlook or extracting the color code of cells. Data preparation, reporting and applications which VBA is commonly used for I would stay away from as there are far better options which have so many less issues. Hope that helps give a little more context to the video. Thanks again for your valuable and insightful comments.
I do programming in VBA and R, while R in general is faster, Excel VBA let you visualise the data and data output much easier and it is less likely for you to make mistakes.
Thanks for your comment. VBA still has its place. If you need programmatic control over any MS office application then VBA is still the best solution. The purpose of the video is to bring greater awareness of the alternatives. I do believe that R is a faster and simpler option for many of the things that are done with VBA today. If you can bypass the Excel interface data transformation and processing is much faster. I would also argue that visualization is better in R as you have so many more options for visualizing your data than Excel. VBA has the macro recorder which helps lower the learning curve but generally produces bad code. R does have a steeper learning curve than Excel but is easier when you get up to speed. You obviously have some programming skills. I would encourage you to use R more. It will do great things for your work and career. I'm working on some more training to help VBA users shortcut the time it takes to get really productive in R at my website www.datastrategypro.com . I hope it can help some people out.
Thanks for the comment. To me RStudio is the modern version way to use R. Not just the IDE but also all of the packages and methodologies created by the RStudio team including Tidyverse, Shiny, Flexdashboards, etc. I can't imagine using R without all of these tools and contributions.
Thanks for the interest. Here is a video I made on R vs Python ruclips.net/video/ETvvwTuiIps/видео.html Here is a video that discusses the different types of tools such as R, Python, Excel, Power BI, databases, Big data and Cloud ruclips.net/video/23QtdnfhBRY/видео.html
Greetings! Thanks for the video. Such a great example. Atm I’m working at the job not related to analysis, but I‘ve been self educating myself in analytic field since Jul 2022 when I have free time and I started learning R several months ago after I had got used to the basics of Python. I’d like to emphasise that once I saw its interface I was disappointed, but then immediately started searching how to change the view, tab positions, e.t.c. and I wanna say I feel R is more closer to me than Python, I will definitely continue enhancing my skills in R over the Python. Talking about Excel…. There’s nothing to discuss mate :D But we still have to use it from time to time. p.s. Lots of companies in Russia still prefer to use it. I also would like to hear your point of view: what hard skills should I develop the most as a future Data Analyst? I meant getting as deeper as I can, since it’s impossible to do everything together at the same time. I’ve already got basics for all programs below. 1. Python / R 2. Power BI / Tableau 3. Excel? (Joking) 4. SQL 5. Sth else? Beat wishes, Anton!
... but this is why Functions exist in Excel. VBA is both Modular and Functional. To not use a set of features properly is not the same as a feature set not existing. You only need to design a Function once for some commonly used calculation, and then you don't have to replicate this in VBA ever again. Any commonly used statistical operation for a given set of data can be setup as a custom function in VBA. If you know how to speed up VBA dramatically by sending sheet data directly to a 2-dimensional Array, and then operating on this array using row cycling, especially if you design with lookup tables to save lookups, you can get code that runs just as quick as R. I mean, a VBA Array equivalence of a VLookup takes like 200 times+ less time than using a sheet function. Good VBA programmers don't ever use the sheet functions, we use multi-dimensional arrays and optimized logic. I know both R and Excel, and usually the criticisms of Excel come via people who don't realize how to structure VBA code to be optimal with the way CPU's process data. The only crappy thing in VBA is that code which could be processed using parallel compute (matrix operations, etc) is bound to single-thread sequential execution, but you can actually get around this by getting your code to initiate multiple instances of Excel in the background: Complicated, but doable; only worth it for very large computation. Excel just has a superior and customizable ability for data-user interact. If you're already in a workplace where the average user who needs to parse data is working with Excel (and wouldn't be using R due to the user interface ;IQ barriers or the average user), why complicate things by introducing R for some work when you can just get someone who knows VBA **properly** to code a function which can solve the problem just as quick as R? Sure, if you know R yourself and you do a lot of adhoc statistical work, definitely just use R, but if you're building something that needs to work with average business users, you're going to have to set something up which is familiar and easy to use, and as I've said, it doesn't have to be slower than R if it is done properly.
I'm putting together a video that will talk about the relevance of DAX, Power Pivot, Power Query and Power BI. In general Power BI, Power Query and Power Pivot without DAX is pretty good as it provides a set of no code tools which are very low learning curve. DAX I think is not so worth it as it does the same thing as R or Python with far less functions, less documentation, less users, less job opportunities and less learning resources. Power BI even has R and Python integration. If you've got the technical skill to learn DAX, don't. Learn R or Python instead. When I need to use Excel, I still do the complex calcs in R and then use Power Query to link in that data like in this video here ruclips.net/video/tRkUzd6PS2o/видео.html Power Pivot is only necessary when the data is really large. No DAX required.
Hey Johnathan, I have a question for you. I am using R to perform a number of data transformations that would probably crash Excel to do. The problem is... My manager wants the output to be an excel file with an Excel Pivot Table. He really wants that pivot table so that if he sees numbers that look off, he can double click the pivot table and get a table automatically generated that contains the makeup of that number. I can easily export the results of my transformations from R to Excel... but how do I create that Pivot Table that he wants?
That's a great question. There are a number of approaches. If your users demand Excel my main recommendation is linking via Power Query as described in this video here. ruclips.net/video/tRkUzd6PS2o/видео.html This takes minutes and so provides the best value to implementation time ratio for people who are still keen to get the data out to Excel. Note that this is only a semi-automated approach. You can easily save and refresh your Pivot Tables but this solution is best if you only have a small handful of files or if the user is responsible for their own refresh. Another approach is to use a Pivot Table library in R like library(pivottabler). If you can convince your users to switch to an R dashboard like I teach here ruclips.net/video/6WTaGEOVJ6s/видео.html this is really the best solution as you'll spend far less development and admin time and have the ability to produce far more interactive dashboards. Thirdly you can use a library like RExcel which gives you a high level of programmatic control over Excel. I actively discourage this solution though as the development and maintenance time is very high. This is not analysis or value-added work and I would never take on this type of work myself. If a client demanded a large number of Excel sheets that involved more than a simple CSV against my recommendation I'd make sure there is enough margin to outsource this or walk away. These jobs are extremely time consuming (much more than the upfront development time) and do not make you a better analyst.
Hi. thanks for this Video. As many companies nowadays tend to implement office 365 in their business. Do you think R and Github could be adapted in such environment. For instance, Is it possible to build up a dashboard in R and share it in sharepoint?
Great question. Dashboards in R are typically published in HTML which means they can be easily viewed through any web browser for free. Although the ideal sharing method for this is a web server, it is very common within companies to simply email these HTML dashboards or post them on some central repository like Sharepoint for easy sharing. Note that R provides free opensource tools that are highly capable of large data processing, statistical analysis, machine learning and presenting of data through dashboards. Often a Microsoft stack will also include Power BI which also provides a good option for dashboards which is easier to pick up but does involve some license cost. If Power BI is available some organizations will continue to serve dashboards through Power BI and also use R for the data processing, statistical analysis and machine learning. Hope that helps
Jonathan Ng thanks a Bunch for this detailed answer. That is very helpful for people like me who are new arrivals in the world of big data. These details are very important to decide which program to start with in the first stage. Thanks again and gd Luck.
@@malekbebane5511 it's my pleasure. It's often hard to know where to start and how deep and how broad to go. I hope to make more content to clarify these points, so please stay tuned and let me know if there are any other points you think need clarification. I think this was a great question and something that others will benefit from as well. Have you seen this video on top tool types yet? ruclips.net/video/23QtdnfhBRY/видео.html
Thanks for your comment. The VBA could be much better. The fact you know about select puts you in the minority of VBA users who know how to code VBA beyond the macro recorder. I apologise if the comparison seems unfair. The VBA code would run substantially faster if it used something like arrays, turned off screen updating, etc. All of this would need to be coded manually and add code overhead that's not necessary with R. I've used VBA for over 10 years and basically wish I had discovered R sooner instead of investing so much time into VBA. Even if you are the most advanced VBA user R is still substantially better for working with data due to it's object model, data structures and vast amounts of libraries. I contemplating for a long time if I should have provided better VBA code in case I end up offending any experienced VBA users. That one slide only appears for a few seconds though and it was really used to illustrate a point which I still believe is valid. I also teach Excel courses, so I know that a lot of things could be done better. The problem is that most people don't even know what the best practices of Excel are, so it massively complicates the message to compare R to Excel techniques that people don't even know about. The goal of this message is to give people a clear understanding of why they might want to use R. Without addressing the key pain points and shortfalls of the most common Excel usage this message would be very difficult to communicate. Moving to R was the best career move I've ever made so I hope you can look past the less than perfect Excel usage and do take the chance to check out R for yourself.
@@DoctorMandible As you're already an experienced VBA user, you'll love it and will make the transition easily. Moving to R is really the first step towards advanced analytics and data science. I've made quite a few tutorials already with the goal of helping Excel users make the transition. I think your existing coding knowledge will give you an appreciation for just how amazing the tool actually is. If you haven't already you can check out some of these on my channel here ruclips.net/channel/UCI6Xkl1reFneR33haWF713A
Hi Jonathan. Thank u for this. Interactive html from R can be ahared as a single html file with all formats and images? Or zipped html file. Please help out
Hi Iqbal, I teach an online course that teaches you how to transition from Excel to R. You can use this link to check out the free preview lectures and purchase the course for just $10 bit.ly/R4Excel
R is a better data analytics tool. Excel is still nice for mocking up a quick dataset, basic calculations or report. It's almost like the pad and paper of data analytics. Low barrier to entry, very flexible, very manual and not scalable. Excel is easier to use for simple analytics problems but very quickly starts to become more complex when you want to go deeper. Excel Power Query is a good no-code way to understand professional data wrangling that is transferable to Power BI for non-coders. If you can learn to code R is one of the best tools an analyst can learn to use. I teach both but prefer to use R for my own analysis work.
Yep VBA has its place and can be very useful at times. Also this VBA is not optimized. It's a recorded macro representing what the majority of VBA users would do. The point is that many people still use VBA primarily for things which are better done with tools such as Power Query, R or Python because they don't understand the alternatives are much easier and robust. Use the right tool for the job. Sometimes that is VBA but more often it is not.
VBA has helped a lot of people out for a long time but it's not built for working with data. It's often the most technical tool in a department which has known nothing better. The alternatives now are easier, faster and free. R is a platform for advanced analytics and data science. VBA is not. VBA will make you a magician in the eyes of the non-technical but will limit you from ever getting a more technical role. Unfortunately, you can't worry too much about people that would rather not change. Change is happening, either way, and you either embrace it or be disrupted by it. Anyone who can actually write VBA would love R. I wish that someone had told me the alternatives to VBA sooner.
@@JonathanNg Hey I completely agree with you! Personally, I prefer Python because I want to understand the lower level details of machine learning algorithms.
@@Ivan-td7kbyou've made some really interesting points. I've also read your comments on my R vs Python video and have added my comments here ruclips.net/video/ETvvwTuiIps/видео.html
@@JonathanNg There is no comparison between R and Excel VBA when talking about accounting stuff particulary agency requirements. Data analysis role of accountants should not be confused with agency-whatever requirements.
@@accountingsapayag thanks for your comment. I agree it's not the best comparison for accounting but then again it also depends what you are trying to do. If you are in the position where you need to clean up data and work with large datasets then R can be a real advantage when Excel starts to struggle. I agree though that there are a lot of use cases that don't fit this and it's much more practical to use Excel. It's hard sometimes to fully explain this. Once you properly know both you can better judge. This video is mostly to show there is another world beyond Excel, which for the right situations can be revolutionary.
While I TOTALLY AGREE that R is better than Excel for this purpose (and I indeed already use R for reporting), this video is waaaay too biased to be taken seriously. I'm sorry, but there are a lot of examples of this, where I'm just thinking "C'mon Jonathan, how low can you go? Are you kidding me?". Ex 1: Excel has array functions on VLOOKUP and Index-Match, so no it is not 1 million calculations. It's 1 base calculations. Ex 2: At 2:15 you would do that calculation in 2 lines, switching after the %>% part. The VBA code he shows afterwards is intentionally made horrible with all the "select" parts and line shifts. Again, indeed R is better here, but why do you sink so low as to not just show the real picture which is like 2 lines vs. 8-10. I mean, come on Jonathan... You should be better than this.
Thanks for your feedback. There are many ways indeed to make Excel much more efficient. I also teach courses on Excel Power Query which is one of the best ways to achieve this within Excel. This video is based on the way that I see 99% of people using Excel. Although array formulas are very powerful I feel that people who are advanced to use array formulas have more than enough skill and will get a lot more power when moving to R. The VBA I agree is not efficient. It's a very basic recorded macro which again is, unfortunately, most of the VBA code I ever see produced by people. Again VBA could be made more efficient and is useful for certain things I don't believe that this is actually something that is easier for most people and R will still generally be faster after all of your Excel VBA optimizations although this will significantly reduce the gap. I personally spent many many years developing solutions using Excel, VBA, array formulas, Power Query, Power Pivot & SQL before discovering R. I didn't even know R existed and wish someone had shared R with me earlier. It's the best tool I've ever had the chance to use as an analyst and it would have saved me years if I had discovered it earlier. Even if I had stumbled across some video on R it would very likely be some stats class that may be difficult to relate to the work done in Excel. This video is designed to help inform people that R even exists and to show that it can be a powerful alternative to some of the work done in Excel. I wanted to show the benefits of R in terms of what would be relatable to the pain points I see from most Excel users without introducing advanced Excel concepts. So yes I fully appreciate that for someone of your skill level this video will come across as a bit biased. The fact that you know R, array formulas and VBA already puts you in the top 1% of Excel users. I have another video comparing Power Query and R which is a bit more objective ruclips.net/video/tNM1fwe6tUc/видео.html I hope that puts this video a little more into context. Thanks again for taking the time to share your feedback.
@@JonathanNg I don't know if people do SQL using provider: ACE.OLEDB.12.0 on an Excel Sheet. It's just way too fast. Above all, It's easy to build a template in excel because one could combine manual calculations for testing results of vba codes. If one should use R, One really has to be sure of ones logic and code. And in the real world to be honest? It's a trial and error but deadlines are almost always non-negotiable. Excel is, and will always be the 'dirty worker' of the reporting world. And when things get too peculiar and urgent... R has to take the back seat. In my very humble opinion--an accountant for 5 years, data analyst/programmer(VBA, python, .NET, java, SQL(MS SQL, Oracle, Access, MySQL) for 15 years.
@@accountingsapayag I appreciate your comments. Trial and error is important. Interactive programming languages such as R and Python allow you to quickly iterate through solutions similar to how you would in Excel. The key benefit is that because R and Python do not alter the original data in the process, it's actually much easier for experimentation. In general VBA means altering the data at each stage, and although you can write code to always work off the original dataset it tends to be a lot more work. I appreciate that Excel tends to be a much more practical reporting solution compared to VBA, .Net, Java, SQL and even Python (unless you're happy to publish as a Jupyter notebook). Personally, I think that RMarkdown is an even more convenient reporting solution than Excel. RMarkdown is as straight forward as a Jupyter notebook and it also publishes to blogs, power point files and web and mobile friendly dashboards with automatic layout of objects. You can even add a download Excel button for your users who want to get to the data. Here is an example of a Dashboard you can implement in R which is very easy to reuse across different datasets ruclips.net/video/6WTaGEOVJ6s/видео.html Unlike other solutions, there's often little, to no extra code required to publish your work and it is far easier to scale a reporting solution in R than it is in Excel. I can build a dashboard pretty fast in Excel but with R I can build a dashboard in the same time that will enable me to generate 1000 permutations of it. You just can't do this in Excel without a lot of VBA code that won't be as good. I still use and teach Excel as well. Excel itself is not a problem. It's just that it's overused in many situations when there are much better solutions because people don't realise there are better solutions. As for integrating SQL with Excel. It works fine if you don't have that much data but it also means that you're working with two tools and bottlenecked by Excel. R will process millions of records of data thousands of times faster than SQL + Excel with a single tool. R uses an in memory column store data structure which is far faster than any relational database. I appreciate that deadlines can sometimes be an obstacle to using something like R over Excel although I would argue this could be one of the reasons to learn to use R instead. R has a steeper learning curve and more initial setup time than Excel which is not going to be good if you're constantly fire fighting. The speed and reusability of something like R though means that if you can spend just a little bit of time to get yourself setup, you'll be getting out your projects out 10x faster than Excel every time. Often Excel and VBA solutions only enable reusability within a very tight set of parameters which means taking the time to set up reusable code bases is just not as worth it as it is for R. I've also spent many years having to deal with urgent deadlines and a range of limited reporting solutions. At the end of the day, you've got to do what you've got to do. I'm probably someone just like you trying to share by far the best solution I've seen in my career of working with all of the other solutions. More than happy to swap notes with other data professionals who have worked through lots of different solutions. I'm always up to figuring out how we can do things better, more enjoyable and make life easier at the same time.
The VBA code here could be 3 lines. One line for the formula and autofilling down. 2nd line Copying. Third line pasting. However, I’m watching this video, because obviously Python/R is better for so many reasons, but you are overselling on the 10 lines vs. 1 line. It’s okay though.
@@Spida7 I appreciate your comment. The VBA code could indeed be either shorter by removing select statements or faster by adding extra lines of code to turn off screen updating or converting the range into an array. Often extra code needs to be written if you don't want to hard code your ranges and this adds additional processing overhead that makes the application run slower. Filling or looping through cells runs thousands of times slower than a vector based operation. It's a fair point that this is not optimal VBA that we are comparing against. When I made this video I recorded a macro like the majority of VBA users would do and then tweaked the code a little to make the range semi-dynamic. In hindsight, I would have optimized the code a little more as it was never the intention to upset more experienced VBA users. The point of this was not to demonstrate best practice VBA code. The point is standard recorded VBA is verbose and slow. Optimized VBA can get fairly complicated (especially when catering for different range sizes) to do what R or Python can do with one line out of the box. If you've got the skills to learn VBA then you want to move to R or Python as soon as you can. VBA does not need to be a stepping stone. Advanced VBA is harder to learn and has no career progression. R or Python can get you into machine learning and data science. So I don't think that 10 vs 1 line is oversold but I do appreciate I could have possibly used a better example.
The point of the video is to show alternatives to the many Excel users who only know Excel. Most Excel users would consider millions of records a distant pipe dream not realizing that it's something that can easily be processed on a standard laptop using the right tools. So side by side comparisons are not actually that useful because you wouldn't do the same things. It's like comparing Excel to a pocket calculator. If you only showed things that a pocket calculator could do, you'd completely miss the point of using Excel. Sames goes for R vs Excel.
That depends on how you use it. Conversations around speed unfortunately rarely talk about architecture and design trade-offs. Anything can be fast or slow depending on how you use it. Even Excel VBA can be fast if you are using arrays and avoiding loops, dynamic calcs and worksheets. Possibly R is slower for certain applications than Sas but more likely you're just using the wrong libraries or using inefficient code.
What I really meant is that R handle big data >500mb much slower than SAS because R load all data into memory while Sas allocate memory dynamically. In clinical trial, I found it is very painful to deal with lab data using r, which tends to be large.
@@doming258 Thanks for the additional details. R is for the most part of it completely memory based (which also means it's very fast if you have enough memory). For loading data faster I recommend using the fread() function from the data.table library which has shown load speeds 40x faster than base R. Also new libraries such as diskframe allow you to also dynamically allocate memory to datasets which are larger than memory.
This also seems a bit outdated given power query/pivot. Excel can handle millions of rows with calculations no problem these days within the data model.
VBA can be pretty handy and can vary a lot from clunky recorded macros to optimized code with arrays. The point of this comparison is really to highlight that there can be much more efficient ways of working. VBA typically works on worksheets, which are slow as you have to hold calculations back and loop through multiple cells. Dataframes work in memory, summarise columns more quickly because they are vectorized, and come with a syntax far richer than SQL for data manipulation. Can you write better VBA than is presented in this video? Absolutely but if you have that level of skill as a programmer you also have a lot of options are your disposal which are often better.
This video is meant for advanced Excel users who are hitting the limits of Excel and who may not be aware of other tools that can make it a lot easier to work with data. I made another video on R vs Python here if that the comparison you were expecting ruclips.net/video/ETvvwTuiIps/видео.html . And this video which covers a wider range of data tools including DBs, data viz tools, cloud computing, etc ruclips.net/video/23QtdnfhBRY/видео.html
![Kodak Black - Sharp Vibes [Official Music Video]](http://i.ytimg.com/vi/oEV0OoLJeIs/mqdefault.jpg)
Join 15,000 to Learn Advanced Data Analytics Skills. Through step by step lectures, complete with downloadable code examples and student support.
R Tidyverse Reporting and Analytics for Excel Users
Level up your advanced data analytic skills. Learn one of the fastest easiest ways to work with large datasets.
www.udemy.com/course/r-tidyverse-reporting-and-analytics-for-excel-users/?referralCode=E6333F435381F1A83CCD
R Shiny Flex Dashboards and Interactive Data Visualizations
The fastest, easiest way to get up to speed with R Shiny to transform your analysis into data driven applications.
www.udemy.com/course/r-shiny-flexdashboards/?referralCode=15405DCD94D9E80B5440
Easy Excel Dashboards, Models, Visualizations with Power Query
Better than VBA. A no code way to easily transform data and build automated reports with Microsoft Excel
www.udemy.com/course/easy-excel-dashboards-models-visualizations-power-query/?referralCode=331E9DFAE5A544402066
What do you want to know about advanced analytics and data science? Leave a comment below.
Very nice comparison, and super easy to understand how powerful R is.
Thanks for your comment. I'm glad you found the video helpful.
very nice and simple explanation. I whis my users understood this too, because they rely too much on excel and then complaint about the limitations of such format... (I'm learning R and it is amazing)
Thanks for your comment. Excel has it's place but if users are hitting limitations or going anywhere VBA then R will be amazing. If people work with data every day then R is a must.
Just a tip: Excel can handle more than 1M rows. It's just displaying that's impossible. Import it via powerquery, export via pivot table or alike to fit the format (
but why would you want to? once i started with R i never even used excel for smaller sheets anymore.
@@rubenthijs746 ah i forgot about that, ive only ever worked in tech jobs that actively encourage leaving spreadsheets to get anything done.
Nice, "R" is always best for Data science..
Whoah, never thought of it, gonna give it a try
Regarding Point 1 and 2:
Power Query, an Excel feature, can handle millions of rows easily. Also, it gives you a visual representation when dealing with data.
Regarding Point 3:
You don't need to code every step done by hand in VBA. This is a beginner's approach. Here is your code rewrited:
--------------------------------------------------------------------------------------------------------------------------------
Sub AddNewColumn()
With Range("J2:J" & Range("A1").CurrentRegion.Rows.Count)
.Offset(-1).Cells(1) = "AnnualBase"
.FormulaR1C1 = "=IF(RC[-4]=""HOURLY"",RC[-9]*40*52,RC[-9]*5*52)"
.Value = .Value
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Or using a variable, if you want to save an extra line:
--------------------------------------------------------------------------------------------------------------------------------
Sub AddNewColumn()
Set Rng = Range("J2:J" & Range("A1").CurrentRegion.Rows.Count)
Rng.Offset(-1).Cells(1) = "AnnualBase"
Rng.FormulaR1C1 = "=IF(RC[-4]=""HOURLY"",RC[-9]*40*52,RC[-9]*5*52)"
Rng.Value = Rng.Value
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Also, you can write functions and subs for every step when working with data, giving it meaningful names, and making it easier to test, mantain and scale.
Regarding Point 4:
Power View or better dashboards referencing a common source of data can do the job regarding showing it inside the company. Since almost all computers in most companies has Excel installed, this is not a problem.
If you want to show data externally, I don't recommend Excel for it. A better option using Microsoft's products is Power BI, which is similar to Power Query, Power Pivot and Power View in Excel.
Regarding Point 5:
Excel, by itself, doesn't have ways to organize collaboration. In the same way, R, as a programming language, doesn't have either. We need other tools to do this job.
P.S.: Git works with Excel files and VBA code.
Excel is a good tool, but you should know how and when to use it. This applies to any other tool on the market.
If you, reader, is considering R or Excel, you can learn both, since they have enough different use cases to be complementary.
Hi Daniel, thanks so much for your in-depth commentary. I agree R and Excel both have their use cases. This video is somewhat simplified to illustrate that there are other applications other than Excel that perform significantly better than the way that most people use Excel. Many people don't know anything other than Excel and naturally progress to recording VBA macros even though this is not the best way to do things. Just because it's still Excel doesn't make it easier.
Thanks for taking the time to refactor the recorded VBA code. The majority of VBA users would not be able to do this which is why I decided to leave the longer code in.
I also teach people power query on this channel as I think it's a great next step for a lot of people. I decided to leave it out of this video as it blurs message.
Power Query is definitely easier and gets you closer to what you can do with a tool like R but R is so much more powerful, easier to automate, build proper applications, easier to explore stats, explore machine learning, give you greater career options. Many advanced Excel users will find R to be one of the best tools they never heard about.
Power Query can only handle millions of records if it bypasses sheets and imports directly into the Power Pivot data model.
VBA as far as I'm aware doesn't really work with Power Query or the Power Pivot Data Model. So if you stick with VBA you are stuck with the limits of cell based Excel which has a practical limit of tens of thousands of records and is extremely slow as it has to calculate each cell instead of entire vector (equivalent to an entire column).
I would still use VBA for the occasional job like, sending emails via outlook or extracting the color code of cells. Data preparation, reporting and applications which VBA is commonly used for I would stay away from as there are far better options which have so many less issues.
Hope that helps give a little more context to the video. Thanks again for your valuable and insightful comments.
Excelent job!
Now I'm in 2019 and your video is a great reference.
Good morning sir
Dr. Bosky Suratwala
Very Informative session
Nicely explained...
I do programming in VBA and R, while R in general is faster, Excel VBA let you visualise the data and data output much easier and it is less likely for you to make mistakes.
Thanks for your comment. VBA still has its place. If you need programmatic control over any MS office application then VBA is still the best solution.
The purpose of the video is to bring greater awareness of the alternatives.
I do believe that R is a faster and simpler option for many of the things that are done with VBA today.
If you can bypass the Excel interface data transformation and processing is much faster.
I would also argue that visualization is better in R as you have so many more options for visualizing your data than Excel.
VBA has the macro recorder which helps lower the learning curve but generally produces bad code.
R does have a steeper learning curve than Excel but is easier when you get up to speed.
You obviously have some programming skills. I would encourage you to use R more. It will do great things for your work and career.
I'm working on some more training to help VBA users shortcut the time it takes to get really productive in R at my website www.datastrategypro.com . I hope it can help some people out.
Amezing Vedio thank you for sharing
Great comparison.. a query though.. the logo used is of rstudio and not r.
Thanks for the comment. To me RStudio is the modern version way to use R. Not just the IDE but also all of the packages and methodologies created by the RStudio team including Tidyverse, Shiny, Flexdashboards, etc. I can't imagine using R without all of these tools and contributions.
Can you make a video on: Python vs R-studio vs Excel vs PowerBI?
Thanks for the interest. Here is a video I made on R vs Python
ruclips.net/video/ETvvwTuiIps/видео.html
Here is a video that discusses the different types of tools such as R, Python, Excel, Power BI, databases, Big data and Cloud
ruclips.net/video/23QtdnfhBRY/видео.html
valuable information. thank you sir
Greetings!
Thanks for the video. Such a great example.
Atm I’m working at the job not related to analysis, but I‘ve been self educating myself in analytic field since Jul 2022 when I have free time and I started learning R several months ago after I had got used to the basics of Python.
I’d like to emphasise that once I saw its interface I was disappointed, but then immediately started searching how to change the view, tab positions, e.t.c. and I wanna say I feel R is more closer to me than Python, I will definitely continue enhancing my skills in R over the Python.
Talking about Excel…. There’s nothing to discuss mate :D But we still have to use it from time to time. p.s. Lots of companies in Russia still prefer to use it.
I also would like to hear your point of view: what hard skills should I develop the most as a future Data Analyst? I meant getting as deeper as I can, since it’s impossible to do everything together at the same time. I’ve already got basics for all programs below.
1. Python / R
2. Power BI / Tableau
3. Excel? (Joking)
4. SQL
5. Sth else?
Beat wishes, Anton!
Great question. I'll see if I can make a video to answer it.
I recorded this answer for you ruclips.net/video/Zqdc29SDpPk/видео.html
Thank you very much sir..
i learnt R before basic spreedsheet functions, so this is a shock
... but this is why Functions exist in Excel. VBA is both Modular and Functional. To not use a set of features properly is not the same as a feature set not existing.
You only need to design a Function once for some commonly used calculation, and then you don't have to replicate this in VBA ever again. Any commonly used statistical operation for a given set of data can be setup as a custom function in VBA.
If you know how to speed up VBA dramatically by sending sheet data directly to a 2-dimensional Array, and then operating on this array using row cycling, especially if you design with lookup tables to save lookups, you can get code that runs just as quick as R. I mean, a VBA Array equivalence of a VLookup takes like 200 times+ less time than using a sheet function. Good VBA programmers don't ever use the sheet functions, we use multi-dimensional arrays and optimized logic.
I know both R and Excel, and usually the criticisms of Excel come via people who don't realize how to structure VBA code to be optimal with the way CPU's process data. The only crappy thing in VBA is that code which could be processed using parallel compute (matrix operations, etc) is bound to single-thread sequential execution, but you can actually get around this by getting your code to initiate multiple instances of Excel in the background: Complicated, but doable; only worth it for very large computation.
Excel just has a superior and customizable ability for data-user interact. If you're already in a workplace where the average user who needs to parse data is working with Excel (and wouldn't be using R due to the user interface ;IQ barriers or the average user), why complicate things by introducing R for some work when you can just get someone who knows VBA **properly** to code a function which can solve the problem just as quick as R?
Sure, if you know R yourself and you do a lot of adhoc statistical work, definitely just use R, but if you're building something that needs to work with average business users, you're going to have to set something up which is familiar and easy to use, and as I've said, it doesn't have to be slower than R if it is done properly.
Why we use R method useful 👍
Comparative analysis done well. Thanks
Very nice comparison
Very good comparison. Thanks!
Thank you for the session.
Hi Jonathan, can you please create a training on how to use Excel DAX?
I'm putting together a video that will talk about the relevance of DAX, Power Pivot, Power Query and Power BI. In general Power BI, Power Query and Power Pivot without DAX is pretty good as it provides a set of no code tools which are very low learning curve. DAX I think is not so worth it as it does the same thing as R or Python with far less functions, less documentation, less users, less job opportunities and less learning resources.
Power BI even has R and Python integration. If you've got the technical skill to learn DAX, don't. Learn R or Python instead.
When I need to use Excel, I still do the complex calcs in R and then use Power Query to link in that data like in this video here ruclips.net/video/tRkUzd6PS2o/видео.html
Power Pivot is only necessary when the data is really large. No DAX required.
Hey Johnathan, I have a question for you. I am using R to perform a number of data transformations that would probably crash Excel to do. The problem is... My manager wants the output to be an excel file with an Excel Pivot Table. He really wants that pivot table so that if he sees numbers that look off, he can double click the pivot table and get a table automatically generated that contains the makeup of that number. I can easily export the results of my transformations from R to Excel... but how do I create that Pivot Table that he wants?
That's a great question. There are a number of approaches. If your users demand Excel my main recommendation is linking via Power Query as described in this video here. ruclips.net/video/tRkUzd6PS2o/видео.html
This takes minutes and so provides the best value to implementation time ratio for people who are still keen to get the data out to Excel.
Note that this is only a semi-automated approach. You can easily save and refresh your Pivot Tables but this solution is best if you only have a small handful of files or if the user is responsible for their own refresh.
Another approach is to use a Pivot Table library in R like library(pivottabler). If you can convince your users to switch to an R dashboard like I teach here ruclips.net/video/6WTaGEOVJ6s/видео.html this is really the best solution as you'll spend far less development and admin time and have the ability to produce far more interactive dashboards.
Thirdly you can use a library like RExcel which gives you a high level of programmatic control over Excel. I actively discourage this solution though as the development and maintenance time is very high. This is not analysis or value-added work and I would never take on this type of work myself. If a client demanded a large number of Excel sheets that involved more than a simple CSV against my recommendation I'd make sure there is enough margin to outsource this or walk away. These jobs are extremely time consuming (much more than the upfront development time) and do not make you a better analyst.
an amazing video, thanks for sharing.
Thanks. I'm glad you found it helpful.
Very informative sir....
nice information
Valuable information
Great video keep going
Thank you. I'm glad you liked it.
Thanks
thank you
Thank you
This video is very helpful .
Thanks a lot 🥳
Highly informative session
Thank you sir
ahhh thanks to you!
Insightful video
lovely video
useful information
Hi Jonathan were to start and how to get expertise. please advise
Thanks sir
thank u
Nice video
Thank u
Thank u for this Jonathon
Do u have experience working with xaringan?
Any way to spread a long table across slides?
Hi. thanks for this Video.
As many companies nowadays tend to implement office 365 in their business. Do you think R and Github could be adapted in such environment. For instance, Is it possible to build up a dashboard in R and share it in sharepoint?
Great question.
Dashboards in R are typically published in HTML which means they can be easily viewed through any web browser for free. Although the ideal sharing method for this is a web server, it is very common within companies to simply email these HTML dashboards or post them on some central repository like Sharepoint for easy sharing.
Note that R provides free opensource tools that are highly capable of large data processing, statistical analysis, machine learning and presenting of data through dashboards.
Often a Microsoft stack will also include Power BI which also provides a good option for dashboards which is easier to pick up but does involve some license cost.
If Power BI is available some organizations will continue to serve dashboards through Power BI and also use R for the data processing, statistical analysis and machine learning.
Hope that helps
Jonathan Ng thanks a Bunch for this detailed answer. That is very helpful for people like me who are new arrivals in the world of big data. These details are very important to decide which program to start with in the first stage. Thanks again and gd Luck.
@@malekbebane5511 it's my pleasure. It's often hard to know where to start and how deep and how broad to go.
I hope to make more content to clarify these points, so please stay tuned and let me know if there are any other points you think need clarification. I think this was a great question and something that others will benefit from as well.
Have you seen this video on top tool types yet? ruclips.net/video/23QtdnfhBRY/видео.html
Nice vedio
I'm interested in R. But this wasn't a fair shake to excel. That VBA code you showed was hideous. For example, there's almost no reason to use Select.
Thanks for your comment. The VBA could be much better. The fact you know about select puts you in the minority of VBA users who know how to code VBA beyond the macro recorder.
I apologise if the comparison seems unfair. The VBA code would run substantially faster if it used something like arrays, turned off screen updating, etc. All of this would need to be coded manually and add code overhead that's not necessary with R.
I've used VBA for over 10 years and basically wish I had discovered R sooner instead of investing so much time into VBA.
Even if you are the most advanced VBA user R is still substantially better for working with data due to it's object model, data structures and vast amounts of libraries.
I contemplating for a long time if I should have provided better VBA code in case I end up offending any experienced VBA users.
That one slide only appears for a few seconds though and it was really used to illustrate a point which I still believe is valid.
I also teach Excel courses, so I know that a lot of things could be done better. The problem is that most people don't even know what the best practices of Excel are, so it massively complicates the message to compare R to Excel techniques that people don't even know about.
The goal of this message is to give people a clear understanding of why they might want to use R. Without addressing the key pain points and shortfalls of the most common Excel usage this message would be very difficult to communicate.
Moving to R was the best career move I've ever made so I hope you can look past the less than perfect Excel usage and do take the chance to check out R for yourself.
@@JonathanNg Definitely going to learn R. Really appreciate your detailed response!
@@DoctorMandible As you're already an experienced VBA user, you'll love it and will make the transition easily.
Moving to R is really the first step towards advanced analytics and data science.
I've made quite a few tutorials already with the goal of helping Excel users make the transition.
I think your existing coding knowledge will give you an appreciation for just how amazing the tool actually is.
If you haven't already you can check out some of these on my channel here ruclips.net/channel/UCI6Xkl1reFneR33haWF713A
Hi Jonathan. Thank u for this. Interactive html from R can be ahared as a single html file with all formats and images? Or zipped html file. Please help out
awesome
yoo this is nice
I am an excel powerpivot user I want to learn R now pls let me know where to learn R
Hi Iqbal, I teach an online course that teaches you how to transition from Excel to R. You can use this link to check out the free preview lectures and purchase the course for just $10 bit.ly/R4Excel
Jonathan Ng can u plz give me ur own course outline so i can decide whether I want to take it or not ?
This videio for learning r
That men r better than excel
R is a better data analytics tool.
Excel is still nice for mocking up a quick dataset, basic calculations or report.
It's almost like the pad and paper of data analytics. Low barrier to entry, very flexible, very manual and not scalable.
Excel is easier to use for simple analytics problems but very quickly starts to become more complex when you want to go deeper.
Excel Power Query is a good no-code way to understand professional data wrangling that is transferable to Power BI for non-coders.
If you can learn to code R is one of the best tools an analyst can learn to use.
I teach both but prefer to use R for my own analysis work.
That example in vba was pretty bad. Vba is oop. Excel/vba has its place
Yep VBA has its place and can be very useful at times. Also this VBA is not optimized. It's a recorded macro representing what the majority of VBA users would do. The point is that many people still use VBA primarily for things which are better done with tools such as Power Query, R or Python because they don't understand the alternatives are much easier and robust. Use the right tool for the job. Sometimes that is VBA but more often it is not.
Good luck telling that to business people and accountants who still thinks VBA is black magic
VBA has helped a lot of people out for a long time but it's not built for working with data. It's often the most technical tool in a department which has known nothing better. The alternatives now are easier, faster and free. R is a platform for advanced analytics and data science. VBA is not. VBA will make you a magician in the eyes of the non-technical but will limit you from ever getting a more technical role. Unfortunately, you can't worry too much about people that would rather not change. Change is happening, either way, and you either embrace it or be disrupted by it. Anyone who can actually write VBA would love R. I wish that someone had told me the alternatives to VBA sooner.
@@JonathanNg Hey I completely agree with you! Personally, I prefer Python because I want to understand the lower level details of machine learning algorithms.
@@Ivan-td7kbyou've made some really interesting points. I've also read your comments on my R vs Python video and have added my comments here ruclips.net/video/ETvvwTuiIps/видео.html
@@JonathanNg There is no comparison between R and Excel VBA when talking about accounting stuff particulary agency requirements. Data analysis role of accountants should not be confused with agency-whatever requirements.
@@accountingsapayag thanks for your comment. I agree it's not the best comparison for accounting but then again it also depends what you are trying to do. If you are in the position where you need to clean up data and work with large datasets then R can be a real advantage when Excel starts to struggle. I agree though that there are a lot of use cases that don't fit this and it's much more practical to use Excel. It's hard sometimes to fully explain this. Once you properly know both you can better judge. This video is mostly to show there is another world beyond Excel, which for the right situations can be revolutionary.
Between R and Python?!?
Great question. I made a whole video about that here ruclips.net/video/ETvvwTuiIps/видео.html
Is that Eddie Woo?
While I TOTALLY AGREE that R is better than Excel for this purpose (and I indeed already use R for reporting), this video is waaaay too biased to be taken seriously. I'm sorry, but there are a lot of examples of this, where I'm just thinking "C'mon Jonathan, how low can you go? Are you kidding me?".
Ex 1: Excel has array functions on VLOOKUP and Index-Match, so no it is not 1 million calculations. It's 1 base calculations.
Ex 2: At 2:15 you would do that calculation in 2 lines, switching after the %>% part. The VBA code he shows afterwards is intentionally made horrible with all the "select" parts and line shifts. Again, indeed R is better here, but why do you sink so low as to not just show the real picture which is like 2 lines vs. 8-10.
I mean, come on Jonathan... You should be better than this.
Thanks for your feedback. There are many ways indeed to make Excel much more efficient. I also teach courses on Excel Power Query which is one of the best ways to achieve this within Excel. This video is based on the way that I see 99% of people using Excel.
Although array formulas are very powerful I feel that people who are advanced to use array formulas have more than enough skill and will get a lot more power when moving to R.
The VBA I agree is not efficient. It's a very basic recorded macro which again is, unfortunately, most of the VBA code I ever see produced by people.
Again VBA could be made more efficient and is useful for certain things I don't believe that this is actually something that is easier for most people and R will still generally be faster after all of your Excel VBA optimizations although this will significantly reduce the gap.
I personally spent many many years developing solutions using Excel, VBA, array formulas, Power Query, Power Pivot & SQL before discovering R. I didn't even know R existed and wish someone had shared R with me earlier. It's the best tool I've ever had the chance to use as an analyst and it would have saved me years if I had discovered it earlier. Even if I had stumbled across some video on R it would very likely be some stats class that may be difficult to relate to the work done in Excel.
This video is designed to help inform people that R even exists and to show that it can be a powerful alternative to some of the work done in Excel. I wanted to show the benefits of R in terms of what would be relatable to the pain points I see from most Excel users without introducing advanced Excel concepts.
So yes I fully appreciate that for someone of your skill level this video will come across as a bit biased. The fact that you know R, array formulas and VBA already puts you in the top 1% of Excel users.
I have another video comparing Power Query and R which is a bit more objective ruclips.net/video/tNM1fwe6tUc/видео.html
I hope that puts this video a little more into context.
Thanks again for taking the time to share your feedback.
@@JonathanNg I don't know if people do SQL using provider: ACE.OLEDB.12.0 on an Excel Sheet. It's just way too fast. Above all, It's easy to build a template in excel because one could combine manual calculations for testing results of vba codes. If one should use R, One really has to be sure of ones logic and code. And in the real world to be honest? It's a trial and error but deadlines are almost always non-negotiable. Excel is, and will always be the 'dirty worker' of the reporting world. And when things get too peculiar and urgent... R has to take the back seat. In my very humble opinion--an accountant for 5 years, data analyst/programmer(VBA, python, .NET, java, SQL(MS SQL, Oracle, Access, MySQL) for 15 years.
@@accountingsapayag I appreciate your comments. Trial and error is important. Interactive programming languages such as R and Python allow you to quickly iterate through solutions similar to how you would in Excel. The key benefit is that because R and Python do not alter the original data in the process, it's actually much easier for experimentation. In general VBA means altering the data at each stage, and although you can write code to always work off the original dataset it tends to be a lot more work.
I appreciate that Excel tends to be a much more practical reporting solution compared to VBA, .Net, Java, SQL and even Python (unless you're happy to publish as a Jupyter notebook).
Personally, I think that RMarkdown is an even more convenient reporting solution than Excel. RMarkdown is as straight forward as a Jupyter notebook and it also publishes to blogs, power point files and web and mobile friendly dashboards with automatic layout of objects. You can even add a download Excel button for your users who want to get to the data.
Here is an example of a Dashboard you can implement in R which is very easy to reuse across different datasets
ruclips.net/video/6WTaGEOVJ6s/видео.html
Unlike other solutions, there's often little, to no extra code required to publish your work and it is far easier to scale a reporting solution in R than it is in Excel.
I can build a dashboard pretty fast in Excel but with R I can build a dashboard in the same time that will enable me to generate 1000 permutations of it.
You just can't do this in Excel without a lot of VBA code that won't be as good.
I still use and teach Excel as well. Excel itself is not a problem. It's just that it's overused in many situations when there are much better solutions because people don't realise there are better solutions.
As for integrating SQL with Excel. It works fine if you don't have that much data but it also means that you're working with two tools and bottlenecked by Excel. R will process millions of records of data thousands of times faster than SQL + Excel with a single tool. R uses an in memory column store data structure which is far faster than any relational database.
I appreciate that deadlines can sometimes be an obstacle to using something like R over Excel although I would argue this could be one of the reasons to learn to use R instead.
R has a steeper learning curve and more initial setup time than Excel which is not going to be good if you're constantly fire fighting. The speed and reusability of something like R though means that if you can spend just a little bit of time to get yourself setup, you'll be getting out your projects out 10x faster than Excel every time.
Often Excel and VBA solutions only enable reusability within a very tight set of parameters which means taking the time to set up reusable code bases is just not as worth it as it is for R.
I've also spent many years having to deal with urgent deadlines and a range of limited reporting solutions.
At the end of the day, you've got to do what you've got to do.
I'm probably someone just like you trying to share by far the best solution I've seen in my career of working with all of the other solutions.
More than happy to swap notes with other data professionals who have worked through lots of different solutions. I'm always up to figuring out how we can do things better, more enjoyable and make life easier at the same time.
The VBA code here could be 3 lines. One line for the formula and autofilling down. 2nd line Copying. Third line pasting. However, I’m watching this video, because obviously Python/R is better for so many reasons, but you are overselling on the 10 lines vs. 1 line. It’s okay though.
@@Spida7 I appreciate your comment. The VBA code could indeed be either shorter by removing select statements or faster by adding extra lines of code to turn off screen updating or converting the range into an array. Often extra code needs to be written if you don't want to hard code your ranges and this adds additional processing overhead that makes the application run slower. Filling or looping through cells runs thousands of times slower than a vector based operation. It's a fair point that this is not optimal VBA that we are comparing against.
When I made this video I recorded a macro like the majority of VBA users would do and then tweaked the code a little to make the range semi-dynamic.
In hindsight, I would have optimized the code a little more as it was never the intention to upset more experienced VBA users.
The point of this was not to demonstrate best practice VBA code. The point is standard recorded VBA is verbose and slow. Optimized VBA can get fairly complicated (especially when catering for different range sizes) to do what R or Python can do with one line out of the box.
If you've got the skills to learn VBA then you want to move to R or Python as soon as you can. VBA does not need to be a stepping stone. Advanced VBA is harder to learn and has no career progression. R or Python can get you into machine learning and data science.
So I don't think that 10 vs 1 line is oversold but I do appreciate I could have possibly used a better example.
Why not show side by side comparisons? Who would be stupid enough to use excel on millions data point calculations? 64k
The point of the video is to show alternatives to the many Excel users who only know Excel. Most Excel users would consider millions of records a distant pipe dream not realizing that it's something that can easily be processed on a standard laptop using the right tools.
So side by side comparisons are not actually that useful because you wouldn't do the same things.
It's like comparing Excel to a pocket calculator. If you only showed things that a pocket calculator could do, you'd completely miss the point of using Excel. Sames goes for R vs Excel.
Compared to Sas, r is slow
That depends on how you use it.
Conversations around speed unfortunately rarely talk about architecture and design trade-offs.
Anything can be fast or slow depending on how you use it.
Even Excel VBA can be fast if you are using arrays and avoiding loops, dynamic calcs and worksheets.
Possibly R is slower for certain applications than Sas but more likely you're just using the wrong libraries or using inefficient code.
What I really meant is that R handle big data >500mb much slower than SAS because R load all data into memory while Sas allocate memory dynamically. In clinical trial, I found it is very painful to deal with lab data using r, which tends to be large.
@@doming258 Thanks for the additional details.
R is for the most part of it completely memory based (which also means it's very fast if you have enough memory).
For loading data faster I recommend using the fread() function from the data.table library which has shown load speeds 40x faster than base R.
Also new libraries such as diskframe allow you to also dynamically allocate memory to datasets which are larger than memory.
Now give R to your secretary
Why is your secretary doing data analysis?
@@jordanzish lol
Why use Excel: it's easier
This also seems a bit outdated given power query/pivot. Excel can handle millions of rows with calculations no problem these days within the data model.
Not entirely accurate re: VBA.
VBA can be pretty handy and can vary a lot from clunky recorded macros to optimized code with arrays.
The point of this comparison is really to highlight that there can be much more efficient ways of working.
VBA typically works on worksheets, which are slow as you have to hold calculations back and loop through multiple cells. Dataframes work in memory, summarise columns more quickly because they are vectorized, and come with a syntax far richer than SQL for data manipulation.
Can you write better VBA than is presented in this video? Absolutely but if you have that level of skill as a programmer you also have a lot of options are your disposal which are often better.
You're comparing it to excel? Really?
This video is meant for advanced Excel users who are hitting the limits of Excel and who may not be aware of other tools that can make it a lot easier to work with data. I made another video on R vs Python here if that the comparison you were expecting ruclips.net/video/ETvvwTuiIps/видео.html . And this video which covers a wider range of data tools including DBs, data viz tools, cloud computing, etc ruclips.net/video/23QtdnfhBRY/видео.html
Thanks
Thank you
thank you
Thank you sir
Thank you
Thank you
Thank you sir
Thank you sir
Thanks