Add-ins for non geolocalized maps

[Eliadann]

Hi all,

I am working on a report about the results of recent elections in Madrid, a few months ago. Data are quite simple, displaying on three different levels, going on district, then to quarters, and finally to sections, that are drawn on a map encircling specific streets.

What I need to do is the following. Whenever I select a particular quarter or district in my pivot table, I need a map to be drawn that "paints" marked areas according to the values shown in the cells. For example, I select certain metric (i.e. abstention) and then the map of the quarter is painted on blue with intensities linked to values in the calls. In the example I share, you can find a simple table along with a map with those areas drawn. These areas are numbered from 117 to 124, which are exactly values displayed in column A of the pivot table.

I understand that there are add-ins or BI solutions that can handle this, but unfortunately I have no experience with any of them, so I´d appreciate any advice on how to face this situation. Please excuse me if I didn´t explain myself properly, english is not my native language

Thanks in advance for your help =)

- Elia

 

[Rijnsent]

Hola Elia,

mmm, in the good old times there used to be a map add-in available in Excel (till version 2000 I guess), then Microsoft decided to go commercial and split off MapPoint ( https://www.microsoft.com/mappoint/en-us/home.aspx ), which still has that option in MS Excel. This site gives some excel options: Making Maps With Excel ~ GIS Lounge MapPoint does cost money though. Another option would be to use Google Spreadsheets and make a map there: Mapping in Google Spreadsheets - Duke Libraries Data & Visualization Services
In any of those cases you'll need to "geo-code" your data: to link a neighboorhood/area/street to coordinates/an area, which might be tricky. Could you show what data/areas you're working with with a dropbox/google drive/etc file?

Thanks,

Koen

 

[Eliadann]

Hiya,

You can find a sample map of the ones I need to work with here below:

https://drive.google.com/file/d/0B965-oUo4J3xTXh1alNBeVROMFU/view?usp=sharing

This is a district map (Madrid is divided in 21 districts). Each district is later divided in quarters (small neighbourhoods) and each of these is divided in sections, which are linked to buildingd where elections are run and marked on the map. Each of these buildings might handle between 15 and 20 sections.

So my concern is, what is the point of geo-coding the data, if what I need is a solution that "paints" a marked area on a map? Around a geo-coded location indeed, but still. I am not really clear how to handle this with a geo-coding solution, so I´d be open to your suggestions. I have Office 365 so I can access to Power BI, but as I´ve said before, I have zero experience with BI solutions

Thanks a lot for your help!

- Elia

 

[Rijnsent]

Hi Elia,

okay, so the levels are distrito-barrio-seccion. I have been doing some online searching checking several Spanish institutions and came up with the following useful links (don't use Power BI myself):
The secciones of Madrid (ETRS89), these are map coordinate files of areas: Cartografía - Ayuntamiento de Madrid
More data on those secciones: Padrn continuo 2010. Distritos y secciones.

And an how-to make a map with imported SHP files: https://blogs.office.com/2015/05/18/power-map-for-excel-may-update-for-office-365/

If all of that is a bit much, you could give https://cartodb.com/ a try, that's pretty user-friendly in making maps (online).

Cheers,

Koen

 

[Eliadann]

That´s a hell of a research Koen, and in a foreign language too. Thanks so much, I´ll work on it asap, have a meeting to present the work tomorrow and this would be a huge improvement to my current results. There is an election to win ahead. Let me know if wanna have a look at the analysis excell file, and will share it with you

Thanks again, will keep you updated

- Elia

 

[Rijnsent]

Hola Elia,
con placer, I've lived 1/2 year in Barcelona and maintained the language with some trips to Latin America, so have no real trouble reading/speaking spanish. It's just that my grammar rusty and I wouldn't know the Excel formulas in spanish (most of them I do know in Dutch & English) . If I can be of any assistance, please let me know, I'd like to give it a shot.
Cheers,
Koen