MS Excel / VBA help thread

So I put I. The url and just followed the options it’s provided.

Will these percentages update automatically? Or is that a specific setting?

No. You can set it to update when the file opens or to regularly refresh after a certain amount of time but it won't keep checking the web page for changes.

OK cool how do you refresh?

https://www.stl-training.co.uk/b/linking-web-data-to-excel/

You can set the data refresh frequency in the properties when you import the data.

Amazing. Thanks very much

Gave this ago today. There was two sections to the page, rates that were already there and rates that I’d chosen options for.

Excel picked out the ones that were already there, but not the ones I’d chosen tick boxes for????

@hippy spam!

Web scraping is inherently a bodge, and doing it in Excel even more so.

Yes it can work, but if you need reliability you should be querying an API.

If you need to scrape, there are better ways, for example a Python script that more specifically targets the values you need, and alerts you if it can't find them, or doesn't give you what is expected.

Excel / power pivot will let you pull the data in by an api. Try that.

If you don’t want to script it

Course, financial data APIs tend to cost, but there's a reason for that.

API… sounds expensive

I’m trying to show on a map which territory someone has at work. I’ve done it on excel. But the map looks weirdly fragmented.

Any ideas?

1 Attachment

Don't do mapping in excel?

is the data complete? looks like you have missing areas in the file

ChatGPT help with Excel questions is pretty good

My map in an excel report seems to be missing areas how can I fix that?
ChatGPT

If the map in your Excel report is missing areas, there are several potential reasons and solutions you can try:

Data Range Issue: Check if the map is using the correct data range. Ensure that all the relevant data is included in the data source for the map. If some data points or areas are missing, they may not be part of the selected range.

Filtering or Data Visibility: Make sure that there are no filters applied that could be hiding certain data points or areas on the map. Check the filtering settings and adjust them as needed.

Incorrect Chart Type: Verify that you are using the correct chart type for the map. Excel offers various chart types, and choosing the wrong one might lead to missing areas. For geographical data, consider using a "Filled Map" or "Map Chart" instead of other chart types like bar or line charts.

Geographical Data Format: Ensure that your geographical data is in the correct format, such as country names, state/province names, or specific geographical identifiers (e.g., postal codes). Mismatched or incorrect geographical data can lead to missing areas on the map.

Missing Data: Check if there are any empty or missing data points in your data source. If a data point is missing, it won't be plotted on the map.

Incorrect Mapping: Double-check if the mapping of data to the geographical regions is accurate. If the mapping is incorrect, the data may not be displayed in the right areas on the map.

Chart Size and Layout: Verify if the size and layout of the chart are appropriate. Sometimes, resizing the chart or adjusting its layout can reveal missing areas.

Data Labels and Colors: If you are using data labels or colors to represent values, check if they are set correctly. Missing areas could be due to incorrectly assigned labels or colors.

Excel Version: Ensure that you are using a relatively recent version of Excel that supports map charts. Older versions of Excel may not have this functionality or may have limitations.

Map Visualization Limitations: Depending on the complexity of your data or the geographical regions involved, Excel's built-in map chart may not be able to display all areas accurately. In such cases, you might consider using specialized mapping software or integrating with external mapping tools.

If you've checked all of the above and are still experiencing issues, you can try recreating the map chart from scratch, as sometimes Excel charts can have glitches that are resolved by starting anew.

Remember to save your Excel report before making changes so you can easily revert if needed.

Thanks peeps

is anyone else getting a lot of cold approaches from companies about Microsoft-funded training for excel and power BI?

Morning all.

I’ve got this spread sheet that I use for work. Specifically there is a column for their account value and a separate column for what they should spend in a year based on their spend so far.

What I want to do is conditionally format the company name cell red, yellow or green if they are within a certain percentage of the account value.

I.e.

LFGSS Ltd account value is £1000, but based on their current spend on going to spend £100 in 12 months - company name in red.

Does that make any sense?

There's not really enough information here - for example, are the % bands the same for all companies?

I would suggest breaking it down to start with - can you expand your example to describe how you would know the company is Amber if you did it manually, including all relevant factors?

It definitely sounds like it should be easy enough (assuming there is consistency in what you want to do).

Usual caveats of are you sure that colouring stuff in is the best solution. You might want some helper columns (e.g. the percentage) to make the formula easier and, more importantly, let you use formulas for analysis and look at things in more detail if required.

I'd write a helper column, so that something like:
if a1 = condition 1 then 1
else
if a1 = condition 2 then 2

etc etc
then you can colour the account cell based on the helper cell and also filter / sum/whatever based on the helper.

or even ICON SETS

Helper columns always, because some poor schmuck will inherit the spreadsheet, and have no clue what to do.

Conditional format formulae are really awkward to derive meaning from, difficult to maintan and build on, and don't translate well to other versions and applications.

So using the example the annual spend should be £1000.

Within 25% of £1000
Within 50% of £1000
Within 75% of £1000

Based on current spending. So I have a revised weekly value column to show the weekly spend and *52 to give me a projected 52 week spend.