(Part 2) Best products from r/excel

I don't have the direct link on hand. But edx.org has a free one that I found useful.

Edit: I purchased this book and it was incredibly helpful. Worth the purchase:

https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395

Also is /u/escobarmiguel90 THEEEEE Miguel Escobar from M is for Data Monkey? Fantastic book - definitely pushed me over the edge in letting go of VBA in favor of Power Query / Get & Transform / Whatever they want to call it next :)

Perfect! just what i was looking for. My OPED list with all the schools has about 4,000 entries, i just didn't copy them over to the google doc.

I'm looking to up my excel game though, have you read this book or any in the series?

Pretty much i've only used excel/spss for basic stats (t tests, averages, and comparisons) and trying over the next 6-12 months make myself a little more marketable for career advancement

I would recommend checking out Learn Excel 2007 through Excel 2010 From MrExcel by Bill Jelen. It is based on common questions from the Mr. Excel website and gives you lots of practical examples, plus sample spreadsheets based on these examples.

I have a different suggestion - try learning Power BI instead. It is the "next step" from Excel and has a lot of deep analytic and data transformation / automation functionality via Power Query and DAX. Tableau is an excellent (and more mainstream) option as well. Also, you may as well start slowly working on Python.

You can through VBA. You need to familiarize yourself with the code behind the website though (his F12 when looking at a web page and it should pull up the background code for you).

First you need to go to VBA then to Tools -> References. Activate the Microsoft HTML Object Library and Microsoft Internet Controls.

Once you've done that, find out where the price is located in the website. Is it always under the same class? Is it under a tag? How is it pinpointed in the code? (you can also right click on the specific element and choose "Inspect Element" or "Inspect" depending on what browser you're using)

If, for example, the Price always shows up under the class name "priceblock_dealprice" (which it does in your link) then you could use the following code:

Sub Macro1()
'
Dim IE As InternetExplorer

Dim html As IHTMLDocument

Dim Price As IHTMLElement

Dim Prices As IHTMLElementCollection

Set IE = New InternetExplorer

IE.Visible = False

IE.navigate ("http://www.amazon.com/dp/B00EA1P8OI/ref=gbph_img_m-3_2922_c8a241ee?smid=AZF6YB7UVA7OU&pf_rd_p=2323902922&pf_rd_s=merchandised-search-3&pf_rd_t=101&pf_rd_i=5550343011&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1JQ0193Q8W4F8R5YWNTF")

Application.Wait (Now() + TimeValue("00:00:09"))

Set html = IE.document

Set Price = html.getElementById("priceblock_dealprice")


Range("A1").Value = Price.innerText


End Sub

EDIT: a simple intro to the new functions:

In order to display the data into your spreadsheet, you need to get the data from the Elements from the webpage (then you can print it using element.value, element.innertext, or element.innerhtml). There are two types of functions for this: getElementBy and getElementsBy. If you do getElementBy, then you can easily print the element value directly into your spreadsheet (as I showed above). If you need to do getElementsBy (and you will depending on how you need to pinpoint the element), then that creates an array of elements, and you need to find out what # in the array your desired element is.

So for example, if we had to pinpoint by Class, and the price we want is the 3rd on the webpage:

Set Prices = html.getElementsByClassName("price")
Set Price = Prices.Item(2) [remember that Arrays start with 0, not 1]

The best advice I can give you is to start with this book by Steven Few: http://www.amazon.com/Show-Me-Numbers-Designing-Enlighten/dp/0970601972/ref=la_B001H6IQ5M_1_1?ie=UTF8&qid=1369652567&sr=1-1. Excel CAN make professional looking charts and graphs. It just doesn't always by default. Steven Few is the authority on this.

I'm not familiar with Business.com, however I am using Lynda.com, Pluralsight.com and MOS 2013 Study Guide for Microsoft Excel to study for my exam. The websites offer training for a plethora of subjects, as to not contradict my previous point of overspending on training. There are a lot of YouTube tutorials that can tutor you through from beginner to advanced Excel, however I prefer to learn from accredited and verified instructors who keep their videos short and to the point.

I don't know how to hyperlink so here is the link to the study guide ($13): http://www.amazon.com/2013-Study-Guide-Microsoft-Excel/dp/0735669201

If you want to learn a lot about PowerPivot and (don't mind reading) I'd recommend anything written by Alberto Ferrari and Marco Russo. They write on PowerPivot / SSAS / Power BI for the Microsoft Press. One of their books was recommended by Michael Alexander who's a Microsoft MVP (I think for Access, but he also knows Excel very, very well.) Take a look at a few of the books below:

https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X/ref=asap_bc?ie=UTF8

https://www.amazon.com/Microsoft-Building-Models-PowerPivot-Business/dp/0735676348/ref=asap_bc?ie=UTF8

I only read a bit of their 2013 book, but it's very comprehensive and of high quality.

I also want to discuss a few other things mentioned here in the comments: PowerPivotPro by Rob Collie and SQLBi. Rob Collie is a former Microsoft engineer on Excel, is an expert on it, and still talks to many engineers on the Excel team. And SQL Bi is run by Marco Russo, who I mentioned above.

My go-to book

As a person in investment banking, the best way to learn forecasting/financial analysis was with a book tailored to this industry.

Macabacus.com is also a phenomenal learning source (and their excel plugin is amazing).

It's designed so you could hand it to someone who's never used excel and to take them up to having a grasp on VBA and some advanced formulas like array formulas. I haven't picked it up in about a year it doesn't sound like it would be a massive learning jump for yourself.

When it comes to charts/reports etc there are actually 2 books that I'm still deciding if I should buy or not. Both have been recomended to myself but I'm waiting until payday before buying them myself so can't testify to how good they actually are.

http://www.amazon.co.uk/dp/1118519655/ref=wl_it_dp_o_pC_S_ttl?_encoding=UTF8&colid=26FRQZR9FPSJT&coliid=I2TLB44KQWBWAS

http://www.amazon.co.uk/dp/1118490428/ref=wl_it_dp_o_pC_nS_ttl?_encoding=UTF8&colid=26FRQZR9FPSJT&coliid=I3LYA71RKLXI19

I’m currently using the Microsoft Sculpt Ergonomic Keyboard which I like a lot. The detached 10 key is a blessing and a curse at times.

https://www.amazon.com/Microsoft-Ergonomic-Keyboard-Business-5KV-00001/dp/B00CYX26BC/ref=mp_s_a_1_3?ie=UTF8&qid=1538101195&sr=8-3&pi=AC_SX236_SY340_QL65&keywords=microsoft+ergonomic+keyboard&dpPl=1&dpID=31dAfO9Ge1L&ref=plSrch

That said the best keyboard I have ever used hands down is the IBM Model 2. But for some the click is too much.

https://www.ebay.com/itm/Vintage-IBM-Model-M-Keyboard-Part-1397721-Mechanical-Clicky-PS-2-Coil-Cord/223156723112?hash=item33f52d5da8:g:UbYAAOSwvl9bpbXi

I have been using older version of this
https://www.amazon.com/Lenovo-ThinkPad-Compact-Keyboard-TrackPoint/dp/B00C32FOY0

I can never go back to anything else. Trackpoint makes it much easier to work in Excel without having to go back and forth with mouse and keyboard.

I can't stand the noise of mechanical keyboards.

I'm considering these two. Leaning more towards the second one based on reviews and me also needing to understand the PowerPoint parts of VBA.

But if anyone has any experience with either and can vouch for the quality of one or both I'd love to hear it.

VB & VBA in a Nutshell: The Language (In a Nutshell (O'Reilly)) https://www.amazon.com/dp/1565923588/ref=cm_sw_r_awd_8PhJwbEJ15WWZ

Mastering VBA for Office 2010 https://www.amazon.com/dp/0470634006/ref=cm_sw_r_awd_qShJwbKDE6RS6

Practical Standards For Visual Basic is a great book for new programmers. Also very cheap on Amazon.

It is good for learning basic syntax along with how to format, comment and error trap code.

https://www.amazon.com/Practical-Standards-Microsoft-Visual-General/dp/0735607338

Excel is a program/tool that should service our sense of design and aesthetics; that is, we shouldn't constrict ourselves to it. Here are my recommendations on learning how to better graph information:

• Tufte is routinely recommended, and I find that he provides comprehensive, practical advice
  
• Another book recommendation but by Few
  
  If you were to post how your graphs currently look along with the data (scrubbed, of course), I'd give a crack at showing you what I'd do.

Man what a fun problem to work out. Just an idea, but if you were going for speed you could buy something like this https://www.amazon.com/Deftun-3-Track-Magnetic-Magstripe-Scanner/dp/B01DVWQ2BO and have people use their license to sign in.

I use something similar to this product on amazon. Mine is just homemade from arduino devices. But I remap and create macro keys to reduce my repetitive workload.

​

https://www.amazon.com/X-keys-Programmable-Keypads-Keyboards-XK-80/dp/B0092SJ8YU/ref=asc_df_B0092SJ8YU/?tag=hyprod-20&linkCode=df0&hvadid=309779531175&hvpos=1o4&hvnetw=g&hvrand=7766966636702391889&hvpone=&hvptwo=&hvqmt=&hvdev=c&hvdvcmdl=&hvlocint=&hvlocphy=9033841&hvtargid=pla-570380111461&psc=1&tag=&ref=&adgrpid=62412137260&hvpone=&hvptwo=&hvadid=309779531175&hvpos=1o4&hvnetw=g&hvrand=7766966636702391889&hvqmt=&hvdev=c&hvdvcmdl=&hvlocint=&hvlocphy=9033841&hvtargid=pla-570380111461

Rob Collie and Bill Jelen's books basically cover the entire thing and are very approachable (they're basically written for Excel power users):

PowerPivot and PowerBI

PowerPivot Alchemy