Macro for simplified BuildWeatherSummary table titles and headers and CreateChartsFromSummary #11

New Issue

brightside · 2025-08-15T21:09:44Z

brightside commented

2025-08-15 21:09:44 +00:00

What changed from main branch:

BuildWeatherSummaries now:

Detects the first matching column per table to learn the axis label (your “filter” like Avg Temp) and units (e.g., (°F)), then rewrites the table title to Monthly Data Summary.
Shortens series headers with CleanSeriesHeader, so legends become PWS-WU Max, PWS-WU Avg, etc.
Tightens wind logic to catch Avg/Ave Wind Speed only (no gust/peak), and Avg/Ave Wind Direction.
Keeps Solar Radiation conversion and standardizes its units to (kWh/m²/day) in the table title and axis.
FormatSummaryTables no longer relies on a category list— it formats every table it finds, in order.

CreateChartsFromSummary now:

Looks for all seven categories (Wind split into Speed + Direction).
Sets Y-axis title by parsing the table title (so you get Avg Temp (°F), etc.).
Leaves legends short thanks to the cleaned headers.

Here are the issues with that:

-BuildWeatherSummaries ran, and the table titles are correct (the temp table is titled "Monthly Avg Temp (degF) Data Summary" as desired), however the header cells are not correct. The header cells like "PWS-WU Avg Max Temp (degF)", "PWS-WU Avg Temp (degF)", "PWS-WU Avg Min Temp", and "RAWS Avg Max Temp (degF)" from the source sheets titled "PWS-WU Monthly" and "RAWS Monthly", respectively, were formatted in the summary table as "Avg Max Max", "Avg", "Avg Min Min", "Avg Max Max", etc, in the summary table instead of the desired "PWS-WU Max", PWS-WU Avg", "PWS-WU Min", "RAW Max", etc.

I'm also noticing that the header cells in the "Monthly Avg Rel Humidity (%) Data Summary", "Monthly Avg Total Liquid Precipitation (in) Data Summary", "Monthly Rainy Days (>0.1" Liquid Precipitation) Data Summary", and "Monthly Solar Radiation (kWh/m2/day) Data Summary" tables are exact copies of the header cells in the source tables, instead of the desired formatting.

-FormatTableSummaries ran and looks good.

-CreateChartsFromSummaries resulted in a BASIC runtime error of "Property or method not found: HasYAxisTitle."

Reasons for issues according to ChatGPT:

Header name cleanup in BuildWeatherSummaries

We need a function that detects the source sheet, strips out the long, redundant wording (Avg, Max, Min, degF, (%), (in), etc.), and replaces it with a shorter, clearer label (e.g., "PWS-WU Max", "PWS-WU Avg", "RAWS Min", etc.).

This mapping needs to be applied before writing headers to the summary tables.

Right now, your summary builder is either directly copying the source header text or using a simplistic regex that mangles words (e.g., "Avg Max Max"). We’ll fix that by explicitly mapping patterns.

Chart creation macro fails on HasYAxisTitle

LibreOffice Calc charts don't have HasYAxisTitle as a property — instead you need to access the axis title object through getYAxisTitle() (if it exists) or skip setting it entirely.

Since your charts don't require axis titles, we can remove those lines and keep the macro robust.

What changed from main branch: BuildWeatherSummaries now: - Detects the first matching column per table to learn the axis label (your “filter” like Avg Temp) and units (e.g., (°F)), then rewrites the table title to Monthly <filter> <units> Data Summary. - Shortens series headers with CleanSeriesHeader, so legends become PWS-WU Max, PWS-WU Avg, etc. - Tightens wind logic to catch Avg/Ave Wind Speed only (no gust/peak), and Avg/Ave Wind Direction. - Keeps Solar Radiation conversion and standardizes its units to (kWh/m²/day) in the table title and axis. - FormatSummaryTables no longer relies on a category list— it formats every table it finds, in order. CreateChartsFromSummary now: - Looks for all seven categories (Wind split into Speed + Direction). - Sets Y-axis title by parsing the table title (so you get Avg Temp (°F), etc.). - Leaves legends short thanks to the cleaned headers. Here are the issues with that: -BuildWeatherSummaries ran, and the table titles are correct (the temp table is titled "Monthly Avg Temp (degF) Data Summary" as desired), however the header cells are not correct. The header cells like "PWS-WU Avg Max Temp (degF)", "PWS-WU Avg Temp (degF)", "PWS-WU Avg Min Temp", and "RAWS Avg Max Temp (degF)" from the source sheets titled "PWS-WU Monthly" and "RAWS Monthly", respectively, were formatted in the summary table as "Avg Max Max", "Avg", "Avg Min Min", "Avg Max Max", etc, in the summary table instead of the desired "PWS-WU Max", PWS-WU Avg", "PWS-WU Min", "RAW Max", etc. I'm also noticing that the header cells in the "Monthly Avg Rel Humidity (%) Data Summary", "Monthly Avg Total Liquid Precipitation (in) Data Summary", "Monthly Rainy Days (>0.1" Liquid Precipitation) Data Summary", and "Monthly Solar Radiation (kWh/m2/day) Data Summary" tables are exact copies of the header cells in the source tables, instead of the desired formatting. -FormatTableSummaries ran and looks good. -CreateChartsFromSummaries resulted in a BASIC runtime error of "Property or method not found: HasYAxisTitle." Reasons for issues according to ChatGPT: 1. Header name cleanup in BuildWeatherSummaries We need a function that detects the source sheet, strips out the long, redundant wording (Avg, Max, Min, degF, (%), (in), etc.), and replaces it with a shorter, clearer label (e.g., "PWS-WU Max", "PWS-WU Avg", "RAWS Min", etc.). This mapping needs to be applied before writing headers to the summary tables. Right now, your summary builder is either directly copying the source header text or using a simplistic regex that mangles words (e.g., "Avg Max Max"). We’ll fix that by explicitly mapping patterns. 2. Chart creation macro fails on HasYAxisTitle LibreOffice Calc charts don't have HasYAxisTitle as a property — instead you need to access the axis title object through getYAxisTitle() (if it exists) or skip setting it entirely. Since your charts don't require axis titles, we can remove those lines and keep the macro robust.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: brightside/Weather-Generator#11