Room block reconciliation - Revision history

Sammich: /* Importing from BACON */ the BACON sheet doesn't have formula cells

2023-09-30T00:36:33Z

Importing from BACON: the BACON sheet doesn't have formula cells

← Older revision		Revision as of 17:36, 29 September 2023
Line 43:		Line 43:
	# Click File → Import and upload the CSV you downloaded from PMA		# Click File → Import and upload the CSV you downloaded from PMA
	# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"		# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"
	~~# Extend the formulas in A:B and M (the gray columns) as necessary~~
	== Importing from the delegate list ==		== Importing from the delegate list ==
	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri for 2021 - entering their name in the Requestor box in RT will bring up their email). It's good to also copy our sales rep (Daniel Pferschy in 2021) so that if staff has changed in Convention Reservations they can find us the right person.		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri for 2021 - entering their name in the Requestor box in RT will bring up their email). It's good to also copy our sales rep (Daniel Pferschy in 2021) so that if staff has changed in Convention Reservations they can find us the right person.

Sammich: /* Creating the Spreadsheet for a New Year */ need to add a series to the chart

2023-09-03T08:44:34Z

Creating the Spreadsheet for a New Year: need to add a series to the chart

← Older revision		Revision as of 01:44, 3 September 2023
Line 12:		Line 12:
	##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates		##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates
	##Move the chart back to the top left		##Move the chart back to the top left
	##Double-click the chart, in the side panel click "Setup", ~~and add~~ G3:G203 to the data range list between D and J (You can type it into the text box or click the little grid to see a nicer looking interface)		##Double-click the chart
			###in the side panel click "Setup",
			###Add G3:G203 to the data range list between D and J (You can type it into the text box or click the little grid to see a nicer looking interface)
			###Under "Series", click "Add Series" and choose the prior year which is now missing from the chart
	#On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)		#On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)
	# On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)		# On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)

Beta: update step 3.7

2023-09-03T08:37:53Z

update step 3.7

← Older revision		Revision as of 01:37, 3 September 2023
Line 12:		Line 12:
	##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates		##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates
	##Move the chart back to the top left		##Move the chart back to the top left
	##Double-click the chart and add G3:G203 to the data range list between D and J		##Double-click the chart, in the side panel click "Setup", and add G3:G203 to the data range list between D and J (You can type it into the text box or click the little grid to see a nicer looking interface)
	#On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)		#On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)
	# On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)		# On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)

Sammich: /* Importing from BACON */ fix import target cell for BACON

2021-10-23T21:30:28Z

Importing from BACON: fix import target cell for BACON

← Older revision		Revision as of 14:30, 23 October 2021
Line 37:		Line 37:
	# At the bottom of the results page under "Query results operations" click "Export"		# At the bottom of the results page under "Query results operations" click "Export"
	# Set the format to CSV and click "Go"		# Set the format to CSV and click "Go"
	# In the Google Sheet, navigate to the "BACON" tab and select cell C2 (the first data cell ~~in the first non-gray column~~)		# In the Google Sheet, navigate to the "BACON" tab and select cell A2 (the first data cell)
	# Click File → Import and upload the CSV you downloaded from PMA		# Click File → Import and upload the CSV you downloaded from PMA
	# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"		# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"

Sammich at 21:02, 23 October 2021

2021-10-23T21:02:48Z

← Older revision		Revision as of 14:02, 23 October 2021
Line 32:		Line 32:
	# Switch to the Reconcile tab and paste special as "values only" at the end of column B		# Switch to the Reconcile tab and paste special as "values only" at the end of column B
	# Extend formula columns C:M as necessary.		# Extend formula columns C:M as necessary.
			# On the "Prices" tab, check that the Passkey total in B2 matches the total room nights picked up on the Passkey dashboard.
	== Importing from BACON ==		== Importing from BACON ==
	# In PMA, select the snafucon db and run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>		# In PMA, select the snafucon db and run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>
Line 42:		Line 43:
	== Importing from the delegate list ==		== Importing from the delegate list ==
	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri for 2021 - entering their name in the Requestor box in RT will bring up their email). It's good to also copy our sales rep (Daniel Pferschy in 2021) so that if staff has changed in Convention Reservations they can find us the right person.		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri for 2021 - entering their name in the Requestor box in RT will bring up their email). It's good to also copy our sales rep (Daniel Pferschy in 2021) so that if staff has changed in Convention Reservations they can find us the right person.
	# Import and reconcile Passkey, so that's up to date.		# Import and reconcile Passkey (see above), so that's up to date.
	# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.		# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.
	# Extend formula columns A and B as necessary.		# Extend formula columns A, B, and L as necessary.
	# Open up the GSR worksheet and the Reconcile worksheet, side by side.		# Open up the GSR worksheet and the Reconcile worksheet, side by side.
	# For each item in the GSR worksheet where the Reconcile column is 0, try to find matching Passkey confirmation code in the Reconcile worksheet, using the name and dates.		# For each item in the GSR worksheet where the Reconcile column is "No" (highlighted yellow), try to find matching Passkey confirmation code in the Reconcile worksheet, using the name and dates.
	#* If you find it, put the GSR confirmation code in the GSR confirmation column (column A) next to the matching Passkey confirmation code.		#* If you find it, put the GSR confirmation code in the GSR confirmation column (column A) next to the matching Passkey confirmation code.
	#* If you don't, put the GSR confirmation number at the end.		#* If you don't, put the GSR confirmation number at the end.

Sammich: /* Importing from the delegate list */ fix contact names

2021-10-03T20:32:18Z

Importing from the delegate list: fix contact names

← Older revision		Revision as of 13:32, 3 October 2021
Line 41:		Line 41:
	# Extend the formulas in A:B and M (the gray columns) as necessary		# Extend the formulas in A:B and M (the gray columns) as necessary
	== Importing from the delegate list ==		== Importing from the delegate list ==
	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in ~~2019 and~~ Daniel Pferschy in 2021~~; GSR email addresses are names with a dot between~~ the ~~first and last names at GrandSierraResort.com)~~.		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri for 2021 - entering their name in the Requestor box in RT will bring up their email). It's good to also copy our sales rep (Daniel Pferschy in 2021) so that if staff has changed in Convention Reservations they can find us the right person.
	# Import and reconcile Passkey, so that's up to date.		# Import and reconcile Passkey, so that's up to date.
	# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.		# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.

TJSomething: Added extra explanation in intro.

2021-10-03T17:25:58Z

Added extra explanation in intro.

← Older revision		Revision as of 10:25, 3 October 2021
Line 1:		Line 1:
	Every year, we have a spreadsheet in Google Docs to help us make sure that we're on target for getting enough rooms to meet our minimums and reconcile hotel-provided delegate lists with digital records provided by hotel reservation services.		Every year, we have a spreadsheet in Google Docs to help us make sure that we're on target for getting enough rooms to meet our minimums and reconcile hotel-provided delegate lists with digital records provided by hotel reservation services.

			We need to perform this reconciliation because the GSR allows for people to book online or by phone. [[BACON]] has a system built in that allows people who book online through the links we provide to automatically get VIP status. We can also login to the GSR's online booking system, [https://planners.passkey.com/ Passkey], to pull a list of all the people who booked through their online system, even if they did not go through our links. However, Passkey cannot give us a list of people who booked by phone. For this, we need the "delegate list": a complete list of all of people who book rooms and then we need to reconcile it against the records from Passkey.
	==Creating the Spreadsheet for a New Year ==		==Creating the Spreadsheet for a New Year ==
	#As the SCIENCE records Google account, copy last year's sheet and share with the same people		#As the SCIENCE records Google account, copy last year's sheet and share with the same people
Line 39:		Line 41:
	# Extend the formulas in A:B and M (the gray columns) as necessary		# Extend the formulas in A:B and M (the gray columns) as necessary
	== Importing from the delegate list ==		== Importing from the delegate list ==
	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019~~, whose~~ email ~~address is her name~~ with a dot between the first and last names at GrandSierraResort.com).		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019 and Daniel Pferschy in 2021; GSR email addresses are names with a dot between the first and last names at GrandSierraResort.com).
	# Import and reconcile Passkey, so that's up to date.		# Import and reconcile Passkey, so that's up to date.
	# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.		# Take the spreadsheet they give us and copy it into the GSR worksheet. It will not necessarily match the columns, so just try to make them match.

Sammich: Update BACON import instructions

2021-10-02T00:00:54Z

Update BACON import instructions

← Older revision		Revision as of 17:00, 1 October 2021
Line 7:		Line 7:
	##Select columns B-D, right-click the column header, and select "Insert 3 right"		##Select columns B-D, right-click the column header, and select "Insert 3 right"
	## Select columns B-D and press Ctrl+C. Select cell E1 and press Ctrl-V, then click the paste popup and choose "values only". Do the same thing again but choose "format only". This gives you a copy of the previous year with static values instead of the formulas.		## Select columns B-D and press Ctrl+C. Select cell E1 and press Ctrl-V, then click the paste popup and choose "values only". Do the same thing again but choose "format only". This gives you a copy of the previous year with static values instead of the formulas.
	##Update the header in ~~cell~~ B1 to the current year		##Update the header in cells B1 and D3 to the current year
	##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates		##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates
	##Move the chart back to the top left		##Move the chart back to the top left
Line 18:		Line 18:
	# On the Reconcile sheet, delete the confirmation numbers in columns A and B		# On the Reconcile sheet, delete the confirmation numbers in columns A and B
	== Importing from Passkey ==		== Importing from Passkey ==
	# Log into [https://planners.passkey.com Passkey].		# Log into [https://planners.passkey.com Passkey]
	# Open the event for the current year.		# Open the event for the current year
	# Click "Room List Report" under the room count.		# Click "Room List Report" under the room count
	# Hover "Actions" in the top right and click "Export"		# Hover "Actions" in the top right and click "Export"
	# In the export dialog select "Comma Separated Value (.csv)" format and click "Export"		# In the export dialog select "Comma Separated Value (.csv)" format and click "Export"
	# In the Google Sheet, navigate to the Passkey tab and select cell H1 (the header of the first non-gray column)		# In the Google Sheet, navigate to the Passkey tab and select cell H1 (the header of the first non-gray column)
	# Click File -> Import and upload the CSV you downloaded from Passkey		# Click File → Import and upload the CSV you downloaded from Passkey
	# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"		# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"
	# Extend the formulas in A:G (the gray columns) as necessary		# Extend the formulas in A:G (the gray columns) as necessary
Line 31:		Line 31:
	# Extend formula columns C:M as necessary.		# Extend formula columns C:M as necessary.
	== Importing from BACON ==		== Importing from BACON ==
	# In the ~~database,~~ run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>		# In PMA, select the snafucon db and run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>
	# Export the ~~result~~ to ~~your favorite spreadsheet format.~~		# At the bottom of the results page under "Query results operations" click "Export"
	# ~~Copy~~ the ~~data without headers~~ to ~~A2 of~~ the BACON ~~worksheet~~ (the first data ~~row of~~ the "~~Confirmation~~" ~~column~~).		# Set the format to CSV and click "Go"
			# In the Google Sheet, navigate to the "BACON" tab and select cell C2 (the first data cell in the first non-gray column)
			# Click File → Import and upload the CSV you downloaded from PMA
			# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"
			# Extend the formulas in A:B and M (the gray columns) as necessary
	== Importing from the delegate list ==		== Importing from the delegate list ==
	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019, whose email address is her name with a dot between the first and last names at GrandSierraResort.com).		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019, whose email address is her name with a dot between the first and last names at GrandSierraResort.com).

Sammich: /* Importing from Passkey */ update Passkey instructions

2021-10-01T22:45:41Z

Importing from Passkey: update Passkey instructions

← Older revision		Revision as of 15:45, 1 October 2021
Line 18:		Line 18:
	# On the Reconcile sheet, delete the confirmation numbers in columns A and B		# On the Reconcile sheet, delete the confirmation numbers in columns A and B
	== Importing from Passkey ==		== Importing from Passkey ==
	# Log into Passkey.		# Log into [https://planners.passkey.com Passkey].
	# ~~Run~~ the ~~full~~ room ~~list report~~.		# Open the event for the current year.
	# ~~Copy columns A to BQ~~ (~~inclusive~~) ~~without headers. The first column should be~~ "~~Hotel Hotel~~" and the ~~last~~ column ~~should be "Locale".~~		# Click "Room List Report" under the room count.
	# ~~Paste them into~~ the Passkey ~~worksheet at C2~~, the ~~first~~ data ~~row with~~ the ~~header~~ "~~Hotel Hotel~~".		# Hover "Actions" in the top right and click "Export"
	# Extend ~~formula~~ columns ~~A, B, and BT~~ as necessary.		# In the export dialog select "Comma Separated Value (.csv)" format and click "Export"
	# ~~Find all the zeroes in~~ the "~~Reconcile~~" ~~column, select the~~ "~~ResAckNo~~" ~~next to them them by Ctrl-clicking~~, ~~and~~ copy ~~them.~~		# In the Google Sheet, navigate to the Passkey tab and select cell H1 (the header of the first non-gray column)
	# ~~Paste~~ special ~~them~~ as values only at the end of column B ~~of the "Reconcile" worksheet.~~		# Click File -> Import and upload the CSV you downloaded from Passkey
	# Extend formula columns C to M as necessary.		# In the import dialog, set the import location to "Replace data at the selected cell" and click "Import data"
			# Extend the formulas in A:G (the gray columns) as necessary
			# Ctrl+Click to select the "ResAckNo" for each row with "No" for "Reconcile" (highlighted yellow), then copy with Ctrl+C
			# Switch to the Reconcile tab and paste special as "values only" at the end of column B
			# Extend formula columns C:M as necessary.
	== Importing from BACON ==		== Importing from BACON ==
	# In the database, run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>		# In the database, run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>

Sammich: Move creating section from the duplicate page

2021-10-01T21:57:11Z

Move creating section from the duplicate page

← Older revision		Revision as of 14:57, 1 October 2021
Line 1:		Line 1:
	Every year, we have a spreadsheet in Google Docs to help us make sure that we're on target for getting enough rooms to meet our minimums and reconcile hotel-provided delegate lists with digital records provided by hotel reservation services.		Every year, we have a spreadsheet in Google Docs to help us make sure that we're on target for getting enough rooms to meet our minimums and reconcile hotel-provided delegate lists with digital records provided by hotel reservation services.
			==Creating the Spreadsheet for a New Year ==
			#As the SCIENCE records Google account, copy last year's sheet and share with the same people
			#On the Prices sheet, update the 100% room-night target and the price schedule
			#On the Previous Year Comparison sheet, archive the old year:
			##Move the chart out of your way so you can see columns A-G
			##Select columns B-D, right-click the column header, and select "Insert 3 right"
			## Select columns B-D and press Ctrl+C. Select cell E1 and press Ctrl-V, then click the paste popup and choose "values only". Do the same thing again but choose "format only". This gives you a copy of the previous year with static values instead of the formulas.
			##Update the header in cell B1 to the current year
			##Set the last cell in column B to the date of the Wednesday before con this year, then fill upwards with earlier dates
			##Move the chart back to the top left
			##Double-click the chart and add G3:G203 to the data range list between D and J
			#On the Master Account sheet, delete all the data - leave the headers and formulas (gray cells)
			# On the Passkey sheet, delete all the data - leave the headers and formulas (gray cells)
			#On the Phone sheet, delete all the data - leave the headers and formulas (gray cells)
			# On the GSR sheet, delete all the data - leave the headers and formulas (gray cells)
			#On the BACON sheet, delete all the data - leave the headers
			# On the Reconcile sheet, delete the confirmation numbers in columns A and B
	== Importing from Passkey ==		== Importing from Passkey ==

	# Log into Passkey.		# Log into Passkey.
	# Run the full room list report.		# Run the full room list report.
Line 11:		Line 26:
	# Paste special them as values only at the end of column B of the "Reconcile" worksheet.		# Paste special them as values only at the end of column B of the "Reconcile" worksheet.
	# Extend formula columns C to M as necessary.		# Extend formula columns C to M as necessary.

	== Importing from BACON ==		== Importing from BACON ==

	# In the database, run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>		# In the database, run <pre>SELECT * FROM `roomblock` WHERE year = (SELECT id FROM years WHERE current = 'current')</pre>
	# Export the result to your favorite spreadsheet format.		# Export the result to your favorite spreadsheet format.
	# Copy the data without headers to A2 of the BACON worksheet (the first data row of the "Confirmation" column).		# Copy the data without headers to A2 of the BACON worksheet (the first data row of the "Confirmation" column).

	== Importing from the delegate list ==		== Importing from the delegate list ==

	# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019, whose email address is her name with a dot between the first and last names at GrandSierraResort.com).		# Email our contact with Convention Reservations at the GSR for a delegate list as a spreadsheet (Joi Cerveri in 2019, whose email address is her name with a dot between the first and last names at GrandSierraResort.com).
	# Import and reconcile Passkey, so that's up to date.		# Import and reconcile Passkey, so that's up to date.