Whata re suggestions for interactive dashboards?

[Oz du Soleil]

I spent this past weekend at a hackathon and found out about R and D3. Now I'm intrigued but don't know how appropriate they are for what I'd like to do.

Here: http://datascopic.net/embedded-excel-dashboard/

I made an interactive dashboard with ExcelWebApp. It's exciting and easy. But now someone wants me to help track his commissions this way. Put it on a web page and set up a login so that only 3 of us can see the data.

With ExcelWebApp:

• Because of the slicers, someone with a Mac can't update the source data.
  
• Security issues around having to use Microsoft's skydrive. wE dont' have any sensitive data, we just don't want the world to be able to see his financial info.

How might I be able to have the source data in Excel and have the person be able to choose things like:

• Auto insurance policies sold YTD
• Auto policies sold in the past 3 years, by month
• What % of commissions are coming from each type of policy?

I don't know any javascript. I took a PHP/MySQL course and got frustrated. After 2 months I couldn't do anything that would only take 10 minutes to do in Excel. So, the learning curve is a big deal.

Can you offer guidance on where I should look?

Thanks much!

[Bruce Mcpherson]

Hi Oz

Very nicely done dashboard. 

As I understand it your main issue it about keeping the data locally but being able to do cool viz type things, even on older excel versions. So only one person works on the data right?

I'm a fan of d3.js, but it's hard work if you don't know javascript. In fact it's pretty complex altogether. I deal with this by generating self contained html, data, javascript, css right out of excel - which keeps everything local - and makes it all about the data. There are some examples here

http://ramblings.mcpher.com/Home/excelquirks/gassites

So it's certainly doable (I'd probably manipulate and send the results in VBA first to keep the vis resuable) - but a steep learning curve  - here's a bunch of cool stuff you could do using the same technique. 

https://github.com/mbostock/d3/wiki/Gallery

You could probably achieve some of the same things with VBA though - given the restrictions on excel versions you have. 

I recommend you play around with some of the d3 tutorials - here's mike bostock (the author's) starter .. http://mbostock.github.io/d3/tutorial/circle.html and see how long before you get frustrated with the thing - when i started i was about ready to jack it in after the 2nd page...

I do recommend that you get some javaScript under your belt though as vba dries up. Google Apps Script is a gentle introduction in a familiar environment

here's a starter - http://ramblings.mcpher.com/Home/excelquirks/gooscript

btw .. i find R unfathomable too..

bruce

 

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Oz du Soleil]

Bruce, thanks for the feedback and links.

The goal is to maintain the source data somewhere that's comfortably secure. We don't have credit card numbers or social security numbers. But at you otherwise described, yes. 1 person would have access to the source data for updating. 2 other people would see the dashboard. Whether the data is stored locally or somewhere more secure than Microsoft's sky drive, that doesn't matter so much.

So, JavaScript is the way to go? That's what I'm getting from you--not only from a narrow goal of developing a dashboard, but in general given all the other apps I build in Excel/VBA.

Thanks for the links to help get me started. That's been an issue: "where to start."

[Bruce McPherson]

Yep .. Even Microsoft are there nowadays...http://msdn.microsoft.com/en-us/library/fp142185.aspx

And course there are zillions of frameworks like jquery to make it easier to do business with, google visualiation api is also pretty good for dashboards...

Sent from my iPad