Excel dashboards intagration with a mac

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the mac
version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel

Actually, to be more precise, I can write a query in a .txt file and feed
it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

··· Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a écrit : > > Hi everybody, > > Has anybody tried to set up an excel dashboard using a mac ? > I'm having some difficulties to replicate the query function with the mac > version of Excel 2016. > > Any help would be much appreciated. > > Thanks in advance ! > > Michel >

Hi Michel

I’ll be very interested to see the responses you get to this. We produce
dashboards using Mac Excel 2016 and had to give up trying to link directly.
Now we download the data to the browser, save the page, open it in Excel
and copy the data and paste it into the dashboard workbook. A faff but it
works. See http://colalife.org/dashboards

My view is that Excel 2016 is still just an advanced beta release!! Not
sure how Microsoft get away with it!

Simon

··· On Fri, Feb 3, 2017 at 3:32 PM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Actually, to be more precise, I can write a query in a .txt file and feed
it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a
écrit :

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the mac
version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel


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


Please excuse any syntax errors. This email was sent from my phone.
07932 107109.

Hi Simon,

I totally agree with your vision of Microsoft Excel for Mac…
Unfortunately Excel is still a tool we can not work without.

I’ve take a look at your dashboards, and I must recon, they are just
amazing ! Is it too much to ask for an example of the Excel file in order
to see how its made ?

Feel free to pass on that !

Cheers,

Michel

··· 2017-02-03 19:21 GMT+01:00 Simon Berry :

Hi Michel

I’ll be very interested to see the responses you get to this. We produce
dashboards using Mac Excel 2016 and had to give up trying to link directly.
Now we download the data to the browser, save the page, open it in Excel
and copy the data and paste it into the dashboard workbook. A faff but it
works. See http://colalife.org/dashboards

My view is that Excel 2016 is still just an advanced beta release!! Not
sure how Microsoft get away with it!

Simon

On Fri, Feb 3, 2017 at 3:32 PM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Actually, to be more precise, I can write a query in a .txt file and feed
it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a
écrit :

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the mac
version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel


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


Please excuse any syntax errors. This email was sent from my phone.
07932 107109.


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/
topic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Hi Michel

Here’s our latest dashboard. Ignore rows 2 to 8 on the ‘data analysis’ tab

  • this is redundant table.

Raw data is pasted into columns A to AK in the ‘Raw data’ tab where columns
AL to AV do some initial calculations.

Table 1 on the ‘data analysis’ tab summarises the data by Province/District
for all districts in Zambia and then Table 2 on the same tab summarises
this data according to our needs and it’s this data that forms the charts
on the dashboard. The only really tricky bit is the table at BF8:BL37 which
displays the clustered, stacked bar chart which is not a chart format
offered by Excel (see Graph 2 on the dashboard).

We have an Excel Template of the dashboard which is the starting point each
week and this has columns A to AK blank on the Raw data tab and just the
first row of columns AL to AV containing formula. Into this we paste in the
data (at A2) and fill down the formulae in columns AL to AV. We found we
had to do this because the referencing got corrupted if we had formulae in
place all the way down when we pasted in the data (another Excel bug??).

I need to acknowledge the help I got from Abbott Katz to get me started.

Happy to answer any questions you may have.

With best wishes

Simon
6/2/17

Simon Berrycounterpart programme manager | Kit Yamoyo Transition to Scale
(KYTS)founder and ceo | ColaLifeLinkedIn
https://www.linkedin.com/in/s1m0nb3rry+260 9755 72175 (Zambia - from
24-Jan-17) | +44 7932 107109 <+44%207932%20107109> (UK - until
23-Jan-17)whatsapp: +44 7932 107109 <+44%207932%20107109> | skype: sxberry
| simon@colalife.org simon@colalife.org | colalife.org
http://colalife.org/ColaLife: current status http://colalife.org/status

KYTS Dashboard R7_5 2359 31-Jan-17.xlsx (1.45 MB)

··· On 6 February 2017 at 18:53, Michel Akkaoui - de Sousa wrote:

Hi Simon,

I totally agree with your vision of Microsoft Excel for Mac…
Unfortunately Excel is still a tool we can not work without.

I’ve take a look at your dashboards, and I must recon, they are just
amazing ! Is it too much to ask for an example of the Excel file in order
to see how its made ?

Feel free to pass on that !

Cheers,

Michel

2017-02-03 19:21 GMT+01:00 Simon Berry simon@colalife.org:

Hi Michel

I’ll be very interested to see the responses you get to this. We produce
dashboards using Mac Excel 2016 and had to give up trying to link directly.
Now we download the data to the browser, save the page, open it in Excel
and copy the data and paste it into the dashboard workbook. A faff but it
works. See http://colalife.org/dashboards

My view is that Excel 2016 is still just an advanced beta release!! Not
sure how Microsoft get away with it!

Simon

On Fri, Feb 3, 2017 at 3:32 PM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Actually, to be more precise, I can write a query in a .txt file and
feed it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a
écrit :

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the
mac version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel


You received this message because you are subscribed to the Google
Groups “commcare-users” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Please excuse any syntax errors. This email was sent from my phone.
07932 107109.


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


You received this message because you are subscribed to the Google Groups
“commcare-users” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Great Thanks a lot Simon.
I admit this will be a huge help to build our dashboards.

If someone from Dimagi could help us regarding the direct link between
CommCare’s Excel Dasbord Integration and Excel 2016 for Mac, that would be
a must.

Michel

··· 2017-02-06 19:00 GMT+01:00 Simon Berry :

Hi Michel

Here’s our latest dashboard. Ignore rows 2 to 8 on the ‘data analysis’ tab

  • this is redundant table.

Raw data is pasted into columns A to AK in the ‘Raw data’ tab where
columns AL to AV do some initial calculations.

Table 1 on the ‘data analysis’ tab summarises the data by
Province/District for all districts in Zambia and then Table 2 on the same
tab summarises this data according to our needs and it’s this data that
forms the charts on the dashboard. The only really tricky bit is the table
at BF8:BL37 which displays the clustered, stacked bar chart which is not a
chart format offered by Excel (see Graph 2 on the dashboard).

We have an Excel Template of the dashboard which is the starting point
each week and this has columns A to AK blank on the Raw data tab and just
the first row of columns AL to AV containing formula. Into this we paste in
the data (at A2) and fill down the formulae in columns AL to AV. We found
we had to do this because the referencing got corrupted if we had formulae
in place all the way down when we pasted in the data (another Excel bug??).

I need to acknowledge the help I got from Abbott Katz to get me started.

Happy to answer any questions you may have.

With best wishes

Simon
6/2/17

Simon Berrycounterpart programme manager | Kit Yamoyo Transition to Scale
(KYTS)founder and ceo | ColaLifeLinkedIn
https://www.linkedin.com/in/s1m0nb3rry+260 9755 72175 (Zambia - from
24-Jan-17) | +44 7932 107109 <+44%207932%20107109> (UK - until
23-Jan-17)whatsapp: +44 7932 107109 <+44%207932%20107109> | skype: sxberry
| simon@colalife.org simon@colalife.org | colalife.org
http://colalife.org/ColaLife: current status http://colalife.org/status

On 6 February 2017 at 18:53, Michel Akkaoui - de Sousa <akkaouim@gmail.com wrote:

Hi Simon,

I totally agree with your vision of Microsoft Excel for Mac…
Unfortunately Excel is still a tool we can not work without.

I’ve take a look at your dashboards, and I must recon, they are just
amazing ! Is it too much to ask for an example of the Excel file in order
to see how its made ?

Feel free to pass on that !

Cheers,

Michel

2017-02-03 19:21 GMT+01:00 Simon Berry simon@colalife.org:

Hi Michel

I’ll be very interested to see the responses you get to this. We produce
dashboards using Mac Excel 2016 and had to give up trying to link directly.
Now we download the data to the browser, save the page, open it in Excel
and copy the data and paste it into the dashboard workbook. A faff but it
works. See http://colalife.org/dashboards

My view is that Excel 2016 is still just an advanced beta release!! Not
sure how Microsoft get away with it!

Simon

On Fri, Feb 3, 2017 at 3:32 PM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Actually, to be more precise, I can write a query in a .txt file and
feed it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a
écrit :

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the
mac version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel


You received this message because you are subscribed to the Google
Groups “commcare-users” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Please excuse any syntax errors. This email was sent from my phone.
07932 107109.


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


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


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/
topic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Hi Everyone,

We had a previous thread about authentication with Power BI and Excel. I’m
not sure how related this is to Excel for Mac, but please see my previous
email below:

··· -------------------

It looks like Power BI/ Excel Power Query doesn’t properly handle basic
authentication. See this article:
https://social.technet.microsoft.com/Forums/en-US/3d81cbcf-2510-41bb-b325-
693d986d6641/power-query-vs-basic-authentication-webserver-with-ssl?forum=
powerquery

You can get around this by adding your own custom power query expression.

  • In Excel, go to New Query, but choose Other, Blank Query
  • In the query editor, then click on View, Advanced Editor
  • Then paste in the following:

let
Source = Web.Page(Binary.Buffer(Web.Contents("")))
in
Source

Note that the URL needs to end with a slash. Excel will prompt you to enter
your username / password via Basic Auth then. You can then use the query
editor to choose the data you want.

On Tue, Feb 7, 2017 at 5:35 AM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Great Thanks a lot Simon.
I admit this will be a huge help to build our dashboards.

If someone from Dimagi could help us regarding the direct link between
CommCare’s Excel Dasbord Integration and Excel 2016 for Mac, that would be
a must.

Michel

2017-02-06 19:00 GMT+01:00 Simon Berry simon@colalife.org:

Hi Michel

Here’s our latest dashboard. Ignore rows 2 to 8 on the 'data analysis’
tab - this is redundant table.

Raw data is pasted into columns A to AK in the ‘Raw data’ tab where
columns AL to AV do some initial calculations.

Table 1 on the ‘data analysis’ tab summarises the data by
Province/District for all districts in Zambia and then Table 2 on the same
tab summarises this data according to our needs and it’s this data that
forms the charts on the dashboard. The only really tricky bit is the table
at BF8:BL37 which displays the clustered, stacked bar chart which is not a
chart format offered by Excel (see Graph 2 on the dashboard).

We have an Excel Template of the dashboard which is the starting point
each week and this has columns A to AK blank on the Raw data tab and just
the first row of columns AL to AV containing formula. Into this we paste in
the data (at A2) and fill down the formulae in columns AL to AV. We found
we had to do this because the referencing got corrupted if we had formulae
in place all the way down when we pasted in the data (another Excel bug??).

I need to acknowledge the help I got from Abbott Katz to get me started.

Happy to answer any questions you may have.

With best wishes

Simon
6/2/17

Simon Berrycounterpart programme manager | Kit Yamoyo Transition to
Scale (KYTS)founder and ceo | ColaLifeLinkedIn
https://www.linkedin.com/in/s1m0nb3rry+260 9755 72175 (Zambia - from
24-Jan-17) | +44 7932 107109 <+44%207932%20107109> (UK - until
23-Jan-17)whatsapp: +44 7932 107109 <+44%207932%20107109> | skype: sxberry
| simon@colalife.org simon@colalife.org | colalife.org
http://colalife.org/ColaLife: current status http://colalife.org/status

On 6 February 2017 at 18:53, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Hi Simon,

I totally agree with your vision of Microsoft Excel for Mac…
Unfortunately Excel is still a tool we can not work without.

I’ve take a look at your dashboards, and I must recon, they are just
amazing ! Is it too much to ask for an example of the Excel file in order
to see how its made ?

Feel free to pass on that !

Cheers,

Michel

2017-02-03 19:21 GMT+01:00 Simon Berry simon@colalife.org:

Hi Michel

I’ll be very interested to see the responses you get to this. We
produce dashboards using Mac Excel 2016 and had to give up trying to link
directly. Now we download the data to the browser, save the page, open it
in Excel and copy the data and paste it into the dashboard workbook. A faff
but it works. See http://colalife.org/dashboards

My view is that Excel 2016 is still just an advanced beta release!! Not
sure how Microsoft get away with it!

Simon

On Fri, Feb 3, 2017 at 3:32 PM, Michel Akkaoui - de Sousa < akkaouim@gmail.com> wrote:

Actually, to be more precise, I can write a query in a .txt file and
feed it to excel. But I’m stuck with a Django error :

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

https://dl.dropboxusercontent.com/spa/enxbvrwkrbi5b9e/l62lb7kn.png

The login and password used for CommCare HQ doesn’t work here.

Any idea ?

Michel

Le vendredi 3 février 2017 14:21:48 UTC+1, Michel Akkaoui - de Sousa a
écrit :

Hi everybody,

Has anybody tried to set up an excel dashboard using a mac ?
I’m having some difficulties to replicate the query function with the
mac version of Excel 2016.

Any help would be much appreciated.

Thanks in advance !

Michel


You received this message because you are subscribed to the Google
Groups “commcare-users” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Please excuse any syntax errors. This email was sent from my phone.
07932 107109.


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


You received this message because you are subscribed to the Google
Groups “commcare-users” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


You received this message because you are subscribed to a topic in the
Google Groups “commcare-users” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-users/krsuG6ISUTg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


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


Sheel Shah
Project Manager | Dimagi
m: +1.781.428.5419 | skype: sheel_shah