Solution for MAC Excel power users

Question

Until MAC for Excel makes VBA fully integrated. Also the data integration is also not good. For that reason I found out two alternative solution rather than using windows for Excel.

1. Use Actual tech add-in that solves two problems:

a) No problems using external data sources via pivot table.

b) No data connection problems using SQL and other sources and it does not crash.

2. Replacing VBA to Phyton is really productive. Many features can be done like macros as well Power Queries functions. Key is that you need to have some programming language. Plus Phyton is an open source and much better than VBA in my opinion.

Side note there are many add-in going to come in the future for data analysis for MAC for Excel

This is my alternative solution till MAC for Excel will come with fully integrated VBA, as well other features like macros and power queries functions and hopefully excel application does not crash when dealing with large complex data, which MAC couldn't handle.

Here are links below for review

http://www.actualtech.com

http://xlwings.org

http://opensolver.org/opensolver-2-6-8-oct-2014-with-mac-osx-support/

Answer 1

Hi AtishDas,

1.In Excel 2016 Microsoft tried to bring as many as they could of what Microsoft thought were the most used shortcuts in WIndows Excel to Mac Excel. The trade-off was the loss of keyboard shortcut customization for Mac Excel. Since the ability to customize keyboard shortcuts was a Mac-only feature of Office, the strange logic is now Mac Excel is more like Windows Excel and Mac users should be happier. Well, they're right about "switchers" of Excel to Mac from Windows, which is most users these days, so I can live with this. There's also an ability in Mac OS X to customize keyboard shortcuts. It's not as robust as Excel 2011 and earlier on the Mac.

2. Macros and VBA are the most important feature of Microsoft Office. Extensibility and the ability to build add-ins and applications using the Microsoft Office object model is Microsoft Office's largest feature. Without macros, most users could get by with lesser software such as web apps or LibreOffice.
3. Microsoft's Power BI tools are not free, but they are very very inexpensive. Only certain packages come with the full set of tools. They are a tremendous value. I hope that some day soon they will come to Excel for Mac.
4. You can make a chart from Pivottable in Excel for Mac, but I must admit that the "Recommended Pivot Charts" feature of Excel 2013 and 2016 makes generating a chart from a PivotTable incredibly fast and easy. This is another feature I really want in Excel for Mac. I make lots of PivotCharts.
5. The SQL and VB Editors in Office 2016 for Mac are abominable. They're pathetic. Whomever programmed these things should be ashamed and embarrassed. If I were Satya Nadella I would be making changes. The 2011 VB Editor was built from scratch and was excellent. The 2016 editor is junk. The MS Query SQL GUI is terrible beyond words.

The product price must be evaluated against time gained or lost by the user and how much the user's time is worth. A free product can cost a lot more than a paid-for product if user productivity is not as good as the paid-for product.

There are lots of **comparisons of R and Excel**. There's no consensus. **This quote** seems to sum things up: "Let me conclude this essay by reiterating this point that there is no perfect tool but the tool suites you best, and sometimes you will need to grasp several tools to use in combination."

Answer 2

Jim:

Yes Data connection works really well using ActualTech ODBC driver. Plus I myself replaced python long time in-place of using VBA. I find VBA in excel horrible.

Yes there are few distinct issues for MAC

1. Lack of customization short cut keys. Which could be huge lacking for somebody who is daily crushing financial model. So lacking alt+ shortcut keys could be an issue, but those issues can be easily solved by creating your own short cut key and getting familiar with it
2. Macros? I don't see any use of it? I don't know why people are complaining about this issue
3. Power BI tools- Yes they are useful but there are better BI tools than excel but are not free
4. Pivot chart? Don't see any value
5. VBA editors and SQL editor mode is not at all useful. This I find Microsoft need to be fixed. 

I myself find using Actual Tech plus using back-end python using xlwings works well for me. 

Regarding pivot chart and true data analysis is a joke. Let me tell you. I work as a research analyst in quantitative division in the oil and gas division at a hedge fund. Mostly our group use mainly R or Matlab for really analysis to provide investment recommendation. Right now I am being trained using Matlab.

I myself is in the business side and not purely super quantitative.  So basically, I use excel about 50 percent of my time rest I use Bloomberg terminal to do rest of my data analysis work there. Most of the quantitative people here don't even use Excel. They mostly use R or Matlab to do their complex data analysis and there are so much features like predictive analysis. Rest of the time they use Bloomberg Terminal.

Excel as an application is baby compared to R or Matlab for real data analysis or statitical analysis tool. In my opinion, Excel as a tool was never geared for big data or analytics. It was mainly geared for business majors for roles like financial analyst, accountant, sales or business analyst to use basic to intermediate data analysis tools. I have never seen professionals who are into heavy data intensive roles like financial engineering, data scientist or economist using Excel that much. 

"All I want Microsoft to fix two or three issues for MAC for Excel in the future. 1) The SQL editor mode 2) VBA editor mode 3) Pivot chart would be helpful if they can add. Excel for MAC has come a long way in my opinion.Hopefully those three issues could be fixed in the upcoming year in future software upgrades.

I find Excel for Mac enough to do my work!

Answer 3

I second your strong endorsement of the ActualTech ODBC drivers. They're inexpensive and my experience mirrors your own - they work very well. The Excel 2016 built-in driver supports only SQL-Server. You can do a lot more with the **ActualTech drivers**including using Excel workbooks as data sources and connecting to a wide variety of other data sources.

I am not quite sure I understand your statement that VBA is not integrated with Excel 2016. You can use Visual Basic for Applications with Excel 2016. What's sadly lacking is a Visual Basic Editor. The VB Editor Microsoft provides with Excel 2016 is a joke. Also lacking is a GUI SQL Editor. A working VB Editor and SQL editor are included with Excel 2011, which gives it a huge advantage over the 2016 edition.

That said, new in the 2016 edition of Excel is support for Slicers (making slicers, formatting slicers, and using VBA with slicers). New in the 15.15 update of Excel 2016 is the ability to display and format PivotCharts. Lacking is the ability to actually make a PivotChart. Also new in the 15.15 update is the ability to add a module in the VB Editor, but userforms still can not be made or edited in the VB Editor, which is still stupid about a lot of stuff.