@ShiqiYang2022; @simonepandit; and @Erick11293: tagging you here as for #84. @jc-cisneros and I will handle all of the implementation steps here, but if you have any suggestions/ideas please do let us know in this thread!

@snairdesai Thanks! I'm looking forward to this discussion.

One other desideratum I'd add to your list: So far, I find the easiest & most flexible interface for editing tables to be spreadsheets like Excel or Google Sheets. I think we want whatever solution we settle on here to be close to that level of simplicity & flexibility.

My prior continues to be that we may want to use Excel / Google Sheets rather than keeping everything in Latex/Overleaf. But I'm open to alternatives provided they can hit the desideratum.

@ShiqiYang2022 I am adding you to this issue, as I think this would be a good time to revisit. Let me know if you have any thoughts on the above.

cc @snairdesai @jc-cisneros

@ShiqiYang2022 @snairdesai @jc-cisneros

One other thought here: Whatever we decide here, there may be cases where we still want to output .tex tables directly from R, Stata, or the like. When we do that, we should have a rule that the R (etc.) code that creates tables is separate from the code that does analysis -- i.e., the analysis scripts still output tables as simple matrices of numbers and then a separate script downstream converts the output to formatted LaTex. That way we can revise table formatting without re-running any analysis scripts.

@gentzkow: Thanks for the note here! As an update, @ShiqiYang2022, @jc-cisneros and I met today to discuss this. We came up with 4-5 potential options, but want to explore the feasibility of each a bit more. We will post additional updates here shortly.

Great. Thanks.

To record, my preferred idea so far is to use Excel / Google Sheets / or similar to build tables. I'm imagining something like:

1. For each table, we have a table tab and a raw data tab
2. The raw data tab imports the matrix of numbers from the /analysis/ output (which could be .txt, .csv, .xlsx, etc.)
3. The tables tab has the formatted table and fills the numbers by reference to the raw data tab
4. We figure out some way to automate (re-)importing the raw data and exporting the formatted tables to separate PDFs

It would be great if you guys could make this one of the options you test.

Let's also make sure we don't spend a ton of time exploring options before we've had a chance to sync on them. Fine to take a few hours to check feasibility, but I'd like to check in on what you're thinking about at that point.

Thanks @gentzkow. Based on this preference, our primary proposal (which we will test for feasibility) is as follows:

(1) PI (or, as needed, RA) creates an Excel spreadsheet in the repository (for example, in ~/paper_slides/input), with the two tabs you proposed. At this stage, the "table tab" would include the table skeleton. The "raw" tab would at this stage include random scalars, which will eventually be mapped to the skeleton in "table tab" (through links referencing the sheets -- we need to test this). HA produced a Google Sheet similar to what we envision here on another project (Table 1 - AE).

(2) RA replaces the random numbers in the "raw" tab with the correct computations produced through code (exactly as you note). We would need to ensure the order of the scalars exactly matches the corresponding placement in the "table tab". What's nice about this is that depending on the lfs-filters, we can also track changes to the underlying skeleton with Git (as new, random placeholder scalars will be added/deleted with any edits).

(3) The "table tab" then fills the computed numbers by reference (exactly as you note).

(4) The RA exports the Excel "table tab" sheet to a .tex format, which is then compiled as a PDF in ~/paper_slides. We need to test how straightforward it is to transfer and maintain spacing/text edits/bolding, etc. from an Excel sheet with these formatting edits to .tex.

We don't yet know if this is fully possible (especially linking the scalars across the sheets and how comprehensive the translations from Excel to .tex are), but conceptually it makes sense. In essence, Excel is just replacing the role LyX previously held. We would only need to rerun ~/analysis if changes are made to the underlying computations of the table.

We will prioritize the testing of this proposal above the others we considered -- it seems most likely to deliver what we hope.

@snairdesai Thanks!

First, I didn't mean to suggest that this is the only option you should consider. Happy to hear the other options too if at least one of you thinks they may be better.

Second, a few clarifications on what I'm thinking, using the numbering above.

(1) I think we would want this to be ~/paper_slides/tables. I don't think there is any problem in Excel / Google Sheets referencing cells across sheets. I'm not sure what you mean about random scalars -- I'm imagining that step 1 would be to import the current version of the output and step 2 would be to format the tables, so there wouldn't normally be a point at which we have placeholder values. But we can of course use those where it's helpful.

(2) I think the data import / updating should be automatic. E.g., if we can have the code output directly in .xlsx format (which I think R, Stata, Python can all do), we can link as described here. I didn't understand the sentence beginning "What's nice..."

(4) I'm imagining we export to .pdf not to .tex. The whole point is that we want to be able to use the spreadsheet program's flexible formatting w/out being bound by the limits of LaTex tables.

Thanks @gentzkow! We will think through these clarifications further. Here are our initial replies for now:

First, I didn't mean to suggest that this is the only option you should consider. Happy to hear the other options too if at least one of you thinks they may be better.

Definitely -- our plan is still to explore the other options. Some other considered approaches are in the dropdown below:

We do think the approach you outlined is closest to what we need.

(1) I think we would want this to be ~/paper_slides/tables.

Populating this in ~/paper_slides/tables makes sense to us.

(1) ...I'm not sure what you mean about random scalars -- I'm imagining that step 1 would be to import the current version of the output and step 2 would be to format the tables, so there wouldn't normally be a point at which we have placeholder values.

I think we were thinking about this process in reverse. In our proposal, the PI would first come up with a proposed skeleton format for a table we want to build in Excel, and the RAs would then work towards computing, and then autofilling the required values into the Excel sheet.

It seems that your proposal from 2. (above) is for the RA to first produce the outputs in .xlsx (or .txt/.csv) format, and then for the PI/RA to format the outputs and export these as PDFs (meaning, we generally produce no .tex versions of the tables at all, though we can allow for this if needed).

In our proposal, the RA may start with a clearer sense of the intended deliverable. By referencing a skeleton table with the intended format we hope to arrive at, the RA knows exactly what scalars/outputs to produce, and can write code with this in mind (even if we don't need to automate the format itself in code). Of course, there's nothing preventing us from creating a desired skeleton in advance of creating outputs in either approach. Other than this, they both seem like viable procedures.

(4) I'm imagining we export to .pdf not to .tex. The whole point is that we want to be able to use the spreadsheet program's flexible formatting w/out being bound by the limits of LaTex tables.

• In the last step, it seems we intend to export directly from the formatted Excel to PDF (with no LaTeX formatting). We are not sure that Excel allows for all the formatting we would need to produce publication-ready tables. Is the idea these would be "LaTeX" quality by the time of the export to PDF? If so, we will look into whether this is reasonable.
• If we want the direct conversion from .xlsx to .pdf seamlessly, we would need to update gslab_make to automate the exports (whereas we already have this process defined for .tex to .pdf). We also would need to think about how to import this into the Overleaf draft with a neat format. This should be feasible, but noting it here.

I didn't understand the sentence:

What's nice about this is that depending on the lfs-filters, we can also track changes to the underlying skeleton with Git (as new, random placeholder scalars will be added/deleted with any edits).

Here, we just meant that in our proposal, where the PI directly edits a skeleton Excel sheet in the repo , we can easily track (non-formatting) updates to the underlying spreadsheet and any new placeholder scalars which the PI adds or deletes when proposing changes (if it is not stored with lfs).

Let us know if our understanding here matches your proposed approach.

Thanks @snairdesai. Getting closer to convergence.

• I don't think we should worry at this level about who is doing what (RAs, PIs). Let's just imagine that we have a set of results in R/Stata/Python that we know we'd like to output and format as a table. We all understand what that table should look like. The question is how to implement.

• Excel can definitely format production-ready tables at the same quality level as what can be produced w/ LaTex (or better)

• Once tables are output as PDFs there should be no issue importing them into the LaTex doc (Overleaf, Lyx). They'll be just like any other figure.

• I agree that how to automate the export to PDF is a key issue -- probably the key issue. I'm hoping that we can maybe have a VBA script that we can call from the command line? I'd vote we investigate this first because it may be the key blocker. Note that a key question will be whether we can export to a PDF that is just the size of the table itself, not a full-page sized PDF with the table appearing on part of it.

• I don't think Git/Github can diff (binary) Excel files?

Thanks @gentzkow! Got it, all of your points here make sense.

• I don't think Git/Github can diff (binary) Excel files?

Yes, we were thinking of creating separate CSV/txt versions along with the Excel sheets (which would not preserve the formatting, but should maintain and track the general structure if we populate placeholders). However, we realize now that by simply tracking the results produced from the code (the matrix of numbers from the ~/analysis/ output) as a .csv or .txt file, we would achieve the same goal.

We will keep you posted on progress on this proposal and the additional ones outlined above. For this proposal, we'll start by testing the exports with VBA (including table sizing).

However, we realize now that by simply tracking the results produced from the code (the matrix of numbers from the ~/analysis/ output) as a .csv or .txt file, we would achieve the same goal.

Yes! I agree. If we are going to output from code directly to .xlsx, we should also output to .csv, similar to the way we output two formats for figures to enable diffing.

TODOs for me:

• Automate formatting of significance values for stars.
• See if we can (should?) remove the first prompt asking the user for permissions (I would vote no; this only pops up for each new file on the first time it is run).
• Remove temporary Excel files created during the run.
• Add workable example with longtable.
• Preserve bolding and italicizing of skeletons during automated exports.
• Test on WindowsOS machine and other Mac chips.
  • Works on Apple M2 Chip.
  • Works on Apple Intel Chip.
• Test with different versions of Excel.
• For later: - Switch to formatting all scalars in Excel (rather than storing as strings w/ relevant formatting imposed in R).

@snairdesai Just a reminder that we should iterate on this often -- I'd like to know what you have in mind before you go too deep into a particular solution.

EDITED

@gentzkow: Thanks for the reminder! Let me provide an update on the (exploratory) work I've done here so far.

I've been focusing on your proposed approach above -- and in particular exporting from Excel to PDF and cropping the resulting PDF to only include the table object (from your point below):

I agree that how to automate the export to PDF is a key issue -- probably the key issue. I'm hoping that we can maybe have a VBA script that we can call from the command line? I'd vote we investigate this first because it may be the key blocker. Note that a key question will be whether we can export to a PDF that is just the size of the table itself, not a full-page sized PDF with the table appearing on part of it.

I've written a (rough) function in gslab_make which automates these exports and trimmings. I had some issues with VBA, but I was able to implement this by natively calling Excel with AppleScript (I wrote an analogous process for Windows, but this has not been tested yet). I also confirmed that the linking procedure across sheets works as expected. Here is an outline of the current process:

WIP Procedure (follows comment above)

• We compute matrices of numbers in ~/analysis/output which will eventually fill the corresponding tables in ~/paper_slides (these are stored as both .xlsx and .csv format, for git tracking).
• These files from ~/analysis/output are linked to ~/paper_slides/tables/scalars, and populate when we run this module in ~/paper_slides/tables/scalars.
• For each table, we produce a .xlsx file in ~/paper_slides/tables/skeletons with two sheets:
  • The first sheet stores imports of the matrices of numbers stored in the corresponding ~/paper_slides/tables/scalars file (which is defined based on the call for run_excel()).
  • The second sheet stores the skeleton of the table, with the desired formatting. It is defined with references to the first sheet, and autofills when ~/paper_slides compiles.
• We export the filled Excel sheet to a PDF object, and use pdfCropMargins to trim the resulting PDF to only display the formatted table. An example of a PDF table produced using this method is here (based on Table 1 of the public draft of APD).

I will post a minimal example which you can test shortly, to see if this method is on the right track.

Brilliant! This sounds excellent. : )

@ShiqiYang2022 thanks! addressing the parentheses issue separately sounds good to me.

Lingering TODOs and Tasklist:

• Move the contents of ~/paper_slides/tables/scalars to ~/paper_slides/input/tables.
• Create a tables subdirectory in ~/analysis/output.
• Change the core command name from run_excel() to export_excel_tables().
• Port the table building functionality out of the template structure, and add it to ~/extensions/.
• Write a README for ~/extensions/excel_tables which details the skeleton constructions.
• In the above README, outline how users can export tables from Excel manually, without the Python call.

@jc-cisneros @ShiqiYang2022: as I work in parallel through the final action items, I think we are at a good point for the two of you to test this process, and tinker with the skeleton tables to see if any additional functionalities should be added.

I don't think we should fully build out every imaginable functionality at this stage, but it would definitely help to iterate on necessities to get to a minimal working version (we should already be quite close). Per #85 (comment), let's not worry about the file path issue here.

The action items above have been completed. Following testing by @ShiqiYang2022 and @jc-cisneros, I'll work on ensuring this works across OS/chips.

@ShiqiYang2022 @jc-cisneros Let me know whenever you get a chance to test this out -- thanks for the support!

@snairdesai flagging that I got the error below:

***********************************************************************************************************************************************************************************************************************************************************
* ERROR! The following Python packages were not properly installed: `pywin32`. 
Please ensure that you have the correct conda environment activated.
If you are not using conda, ensure you have installed all dependencies from ~/setup/dependencies.md. *
***********************************************************************************************************************************************************************************************************************************************************

@jc-cisneros I think you'll just need to comment this line out in conda_env.yaml if you're running on a Mac (this will be part of the instructions in the README and should be flagged in conda_env.yaml). Let me know if that doesn't do the trick.

@snairdesai, that indeed fixed it. Got a successful run on my end! Aside from the small inconvenience of manually having to interact with Excel windows to grant permissions, it works smoothly.

@snairdesai the idea is that this would be the default practice, right (i.e., using Excel to create table skeletons)?

Great, thanks @jc-cisneros! That's good to hear. Some quick replies:

@snairdesai, that indeed fixed it. Got a successful run on my end! Aside from the small inconvenience of manually having to interact with Excel windows to grant permissions, it works smoothly.

Agree that this is a bit annoying (especially since permissions must be granted for each additional file in the process). Users can also grant disk access for Excel directly (see here). While this is annoying and we could potentially find a workaround, this is a built-in security feature on Mac devices which I might recommend we do not try bypass.

@snairdesai the idea is that this would be the default practice, right (i.e., using Excel to create table skeletons)?

This I'm not sure about - I think the process is straightforward enough once users run through this a couple of times. I suppose it would depend on users' preferences.

Thanks @snairdesai! I've modified the skeleton and confirm that all the changes are properly reflected in the final output. This properly replaces LyX + tablefill.

Thanks @jc-cisneros! @ShiqiYang2022 Let us know when you get a chance to run through this a final time and we can proceed to merge (maybe it would be useful to test this on Windows as well as your Mac, I definitely expect some bugs to arise there).

@snairdesai Confirming the testing works well on my end! I am attaching the logs below.

One other thing to flag is that, when we run the repository, it is important for us to run it without other Excel files opening. The program will close all existing Excel files instead of only the file which produce the table.

Thanks @jc-cisneros and @ShiqiYang2022! I'll update the instructions to notify users to save any work on Excel (and close windows if they wish). After this, I think we are ready to merge (given that these action items have been addressed).

Next steps (for future issues):

• Resolve syntax warning for escape characters.
• Fix path issue relating to parentheses naming.
• Extend skeleton capabilities to handle more complex formatting needs (these will arise naturally as we begin to use the skeleton for lab table constructions).

cc @gentzkow

Thread continues in the associated PR (#97).