Running weekly statistics

There are several stages to generating the FSPs that contain our England and County Statistics. It is suggested that the stages are followed in the order below.

Updating the spreadsheets

Up-to-date copies of the latest versions of the following spreadsheets have to be downloaded onto a computer.

1. England Unknowns Statistics.xlsm
2. England Unsourced Statistics.xlsm
3. England Unconnected Statistics.xlsm (This file can’t be opened in Excel online because it exceeds the 25MB file size limit. It is necessary to ‘Open in Desktop App’, which means that saved changes are saved in OneDrive. It can then be saved to my laptop.)

Note

i) If Roy has made changes to the spreadsheet itself (e.g. because an additional county has been added or we have decided to extract data from a new source), the latest version of the spreadsheets must be downloaded, replacing the old version. Equally, if I did not run the processes on my laptop the previous week, the latest versions needs downloading (because part of the process is a comparison between this week’s profiles and the profiles in the version of the spreadsheet that is being replaced).

ii) New spreadsheets are downloaded onto my laptop from where I save them to my ‘Wrent and Stats’ folder

iii) The process is managed from the ‘Control’ tab on each of the three spreadsheets.

2) If there is a new version of any spreadsheet (or if my spreadsheet hasn’t been updated the previous week): a) On the newly downloaded spreadsheet i) Enable editing ii) Change cell J2 to C:\Users\steve\downloads\ (ensuring that there is no space after the backslash) iii) Save. b) In the ‘Wrent and Stats’ folder (it’s not always necessary to do this step as the settings are sometimes already correct) i) Right click on the new file ii) Click on ‘Properties’ iii) Tick ‘Unblock’ (bottom right of the Properties box, to enable macros) iv) Click ‘Apply’ v) Click OK. c) On the spreadsheet i) If there is a yellow warning message, “External Connections have been enabled’ click on the “Enable Content’ button.

Stage 2 – Running the spreadsheets 3) Ensure that all downloads from the previous week’s processing have been deleted from the downloads folder on my laptop. (They will be labelled e.g. EnglandUnsourced00, EnglandUnsourced01, EnglandUnsourced02……. etc with similar file names for Unknowns and Unconnected)

4) On each spreadsheet, follow the same process (outlined here for the Unknowns spreadsheet)

a) Click on the Blue ‘Generate Unknowns Statistics’ button in cell C6:C7 b) A message box will appear “Do you want to move all profiles on the Profiles Update sheet to the Profiles Master sheet, overwriting any existing profiles?” i) If for whatever reason Roy has already run the statistics for the week, click ‘No’ ii) If I ran the statistics last week and I am therefore running from a current spreadsheet, click “Yes” c) A macro automatically runs a series of queries in Wikitree plus to download several spreadsheets (due to the maximum spreadsheet download of 20,000 record) which will then be collated into the spreadsheet. The process will run for several minutes. The browser may appear to ‘hang’ with an empty screen. d) Toggle to the spreadsheet and there will probably be a message box “A file named ‘C:\Users\steve\Desktop\Wrent and Statistics\xcl_unknowns_new.csv already exists in this location. Do you want to replace it?” Click the ‘Yes’ box. e) If there is a blank tab open on the browser, it can be closed. f) If the process has run correctly, save the spreadsheet to ‘lock in’ this week’s data. (If there has been some sort of a glitch, don’t save and you can start again from the previous week’s spreadsheet and data. You will have to go back to step 3 and delete any files from the Downloads folder.) g) When all 3 Excel spreadsheets have been processed, proceed to stage 2 Note: I have found that it can cause issues if I work on anything else while the spreadsheets are running. Note that the Unconnected spreadsheet is considerably bigger than the other two.

Stage 3 – Updating the England Unknowns FSP 5) To generate Unknowns report: a. Go to my “Wrent and Stats” folder. b. Open the ‘FSP Text Unknowns’ file c. Click i. Edit ii. Select All iii. Copy a. Open the FSP and in the Editing box i. Right click ii. Select all iii. Delete iv. Paste v. Save Changes Note: I cannot copy the ‘FSP Text Unsourced’ file to a Wikitree FSP as my version of Notepad cannot copy and paste a ‘hard space’. This doesn’t affect the production of accurate statistics on the England and Counties spreadsheet.

Stage 4 – Updating the England and County statistics spreadsheet 2. Run Wrent 3. Open GoogleDrive (myDrive>Wrent SW) and highlight 11 files from the ‘Wrent and Stats (8 ‘Comma Separated Value’ (.csv) files starting with ‘out….’ and 3 files starting ‘xcl…..’) 4. Drag these 11 to the folder in my OneDrive called ‘WRENT SW’ and (unless doing this for the first time) ‘replace existing items’ 5. Open https://docs.google.com/spreadsheets/d/12loZaB4TiYLvhk6atz55xuFJjBI3TzFEz_REZ4kGhi0/edit#gid=189528891 Note: This may take quite a few seconds to fully load before ‘buttons’ appear. a. Change the New Report date in cell F1 to the Saturday’s date just gone. b. Click on Generate Report Sheet (yellow button in B16:B17); and wait till ‘Running Script’ no longer displays. c. Move the newly created tab with this week’s stats to the right of the ‘Template’ tab d. On the Control sheet, click on Generate FSP Text; and wait till ‘Running Script’ no longer displays. 6. Go back to the OneDrive ‘Wrent SW’ folder a. Click F5 b. ‘Right click’ and Open ‘FSP text document’ with Text Editor c. ‘Right click’ and Select All d. ‘Right click’ and Copy 7. Open a ‘mock up’ FSP to create a first draft of the statistics a. Override existing text with new text and Save Changes 8. Review statistics a. Overview of week and cumulative position b. Any special factors affecting this week’s statistics? c. Top performing counties 9. Update Commentary Page a. https://www.wikitree.com/wiki/Space:England_Statistics_Commentary 10. Add generic and/or county specific notes to FSP a. Update O20:O70 on the Control sheet with any generic or county specific notes. b. Re-run ‘Generate FSP Text’ to add the notes c. Copy the commentary to the ‘mock up’ FSP. d. When happy with the completed ‘mock up’: i. Replace text in https://www.wikitree.com/wiki/Space:England%2C_Regional_and_County_Statistics_Page with the text from the mock up, ii. Change the date in the ‘Date or Start Date’ field to the Saturday’s date iii. In the ‘Explain your changes’ box, entering e.g.: ‘Updated stats for database extract of 2024-02-03’