Lo-Fi Python

Strategies for Speeding Up a Google Sheet

Recently I had a Google Sheet bogged down so much, Google prompted me to fix it from consuming too many resources in my browser. So, I pondered how to speed up my Google Sheet and made a short list of ideas. I included some ideas from a great article from Google about Optimizing Google Sheets.

Strategies for Reducing Load Time in a Google Sheet

1. Delete all extra columns and rows. Empty rows and columns make the file larger and need to be loaded everytime a Google sheet is accessed. Highlighting and pressing ctrl+D to delete them means they won't be loaded.

2. Change calculation settings to refresh on a change or "every hour" instead of "every minute". This setting affects functions like NOW(), TODAY() and RAND() are recalculated. Select File > Settings and click the "Calculation" tab to adjust your settings.

3. Remove expensive, unnecessary, or old unused functions. Each function you use adds additional load to the Google Sheet. By removing extra functions and charts, it will speed up your Sheet and consume less compute resources. This is unique to each Sheet. Think to yourself, is this part of the document necessary? Is it giving value, or can it be cut?

4. Reduce the number of tabs. Consolidating or removing extra tabs is another way to speed up your load time.

5. Reference data on the same Sheet when possible.

   Reference your data on the same spreadsheet you work on. This is faster than Import functions, such as: IMPORTRANGE, IMPORTDATA, IMPORTXML, IMPORTHTML

   Source: Google, Optimize your data references to improve Sheets performance

6. Use closed range instead of open range references.

   An open range spreadsheet means the range starts and ends without indicating a specific row or column. Example: A:B means the range that includes all cells in columns A and B.

   A closed range reference refers to the range that starts and ends with a specific row or column. Example:

   A1:B6, A1:C100

   Open range: A:B

   Closed range: A1:B6

   Source: Google, Optimize your data references to improve Sheets performance

7. Avoid long reference chains when possible.

   Reference chains slow down your sheets. For example, in the following case, A2 reads A1 data; A3 reads A2 data; and so on. Because the data is chained together, to calculate the value in A10, Google Sheets waits for all the previous values (A1 to A9) to calculate before it returns the value in A10.

   Source: Google, Optimize your data references to improve Sheets performance

Conclusion

Google Sheets are typically very fast until you reach a certain threshold of density. After you reach that point, these changes can speed it up quite a bit!

Comparing NFL Football Python Packages

In the spirit of the NFL regular season concluding and playoffs beginning soon, I wondered what packages Python had in the NFL stats space. Below, I have compiled my notes below on each of the libraries I found for the NFL in Python.

After a first pass through, there is no runaway favorite. In Python, we say "there should be one obvious way to do it". This doesn't seem the case for NFL stats. In a somewhat rare occurrence, the Python community offers a graveyard of abandoned or incompatible projects with modern Python. Ultimately, this probably stems from the NFL's inability to provide a consistent stats API or feed over time. This may be one of the few times where the R community has better resources available, like the nfldata package in their ecosystem.

Product Market Fit as a Street Clown

I'm a big fan of attempting to draw inspiration from everyday common occurrences. Today, while riding in a car I saw a clown street performer and remarked to myself about the "product market fit" they require to earn a living, or lack thereof.

The street clown is not worried about finding the specific right person for their product. They are also not distracted by getting their messaging right to find exactly their perfect audience. Rather, they find their customers in real time by finding high traffic areas. Once there, they set up at a busy intersection with lots of cars or pedestrians. Then, they seek product market fit by juggling, cycling and doing what clowns do, entertain. People have no choice but to wait at the intersection in their cars. The clown has performed a service for society. Humor or entertainment is gained by their customer. They see the clown and think impulsively, "How funny! I'm feeling generous today." So, a little money changes hands.

The funny thing is that the clown will make some money because people want to support them. Just because they are there. They are in the arena, hustling for a living like all the people scurrying by in their cars. The difference is the people in cars are likely on the way to or from their job, whereas the clown already clocked in.

So what lessons can we learn from a clown performing in the street? Sometimes, we shouldn't be overly concerned with exactly who is our customer. Marketing says conventionally that the more you know about your customer base, the better. However, the street performer clown shows it is not always a requirement. In the clown's case, the requirement is going to high traffic areas and broadcasting your special brand of entertainment.

The clown earns a small tip from many different customers in passing. They have an advantage in a sense, because their diversification of customers is high. They are not overly dependent on any specific members of their audience. This makes their business more resilient, assuming they're taking home enough to make it worth it.

A street entertainer faces a tough prospect: their customers are all going on their way and don't have the desire to be entertained. This doesn't stop the street clown. They put on the face paint and funky clothes. They grab their items to juggle, their unicycle, whatever is needed to support their act. The clown shows up and performs. We would all be more successful if we approached our work like a street performer. Get out there and make it happen. Don't focus too much on who is the perfect customer. Get your product in front of people and see what they think. That's the way of the street clown.

How I Converted My Wordpress Blog to Pelican, A Checklist

Recently there has been a lot of noise online about the Wordpress website platform. This blog is formerly a Wordpress blog, so I am able to share from personal experience how I moved this blog. I began writing the early years of this blog on the Wordpress free plan, followed by a few years as a paying customer of Automattic, the corporate entity associated with Wordpress.org. The notes below are from my experience moving to a Python Pelican blog.

Here is the checklist I wrote down when I made than transition:

1. Export posts to XML.
2. Export all media.
3. Convert posts to new blog format: Markdown or reStructuredText Format
4. Set DNS redirect from old blog to new blog.
5. Update old blog with notice of new blog.
6. Set posts to private/public and set search engine + "third party" AI training settings.
7. Update urls from old domain to new domain.

Install the Pelican, lxml, beautiful soup and feedparser Python libraries.

pip install pelican
pip install BeautifulSoup4
pip install lxml
pip install feedparser

1. Export posts to XML.

Go into the Wordpress settings and export posts. I selected XML format.

2. Export all media.

Wordpress allowed me to choose to export posts or media, so I exported all the images on my blog also.

3. Convert posts to new blog format: Markdown or reStructuredText Format.

Pelican's import tool for wordpress converts your XML file to either .md or .rst files based on which CLI argument you pass.

Use the pelican-importer CLI to convert the XML to Markdown or reStructuredText.

I chose to use the default detting of the CLI to export to .rst. If you want to specify a folder to drop the contents, use the -o argument. Use the -m argument to specify which output format.

1
2
3
4

# Convert XML to reStructuredText Format files with default settings.
pelican-import --wpfile example.wordpress.2023-05-14.000.xml
# Convert XML to Markdown files and specify the output directory and format.
pelican-import --wpfile example.wordpress.2023-05-14.000.xml -o ~/projects/example.com/content/blog -m MARKDOWN

pelican-importer documentation

4. Set DNS redirect from old blog to new blog.

I previously hosted my blog on a custom .com domain. When I stopped paying for the domain, my domain reverted back to example.wordpress.com. However, you can pay a domain register a small fee to forward traffic to your new domain. You can transfer old domain to services like Namecheap or Cloudflare to set up a redirect. It's recommended to keep a redirect for at least 1 or 2 years after moving to a new domain to catch any evergreen backlinked traffic.

5. Update old blog with notice of new blog.

Keep A Few Posts Exclusively on the Old Blog

The benefit of Worpdress's free plan is that you can still keep some posts and the original sub-domain live on their free plan.

Write your final post on the old blog to let subscribers know you've moved.

I had built up a list of subscribing Wordpress users after 6 years writing on the platform. Write a brief post explaining where to find future blog posts to let your audience know where to find you. In my case, I wrote a goodbye post on my old blog, and an announcement post about on my new blog domain.

Update the Site Tagline on Your Old Blog

I also set the old blog's tagline to point readers to new blog. In Wordpress Settings / General, you can edit the site tagline:

6. Set posts to private/public and set search engine + "third party" AI training settings.

Once I got the hang of Pelican, I reviewed each converted post to fix links, grammar or formatting errors from the conversion. I slowly moved my converted posts over in batches of 2 or 3 posts at a time. As I moved the posts to the new blog, I chose to set the old posts individually to private within the Wordpress editing CMS, rather than at the sitewide level. By setting posts to private, they're also still accessible to your Wordpress subscribers by direct url link. Setting individual posts to private allows subscribers who are following legacy links to have a chance to find my new home on the web. For non-subscribers, the private links will break, however they will still see an error page on your old blog. From there, they might see the site tagline directing to the new blog or find a few of the posts I reserved exclusively for the legacy blog.

One benefit of Wordpress from an SEO perspective is that they have search engine indexing control in the settings panel. In the settings, you have the ability to tell search engines whether or not you should index your blog. I set my old Wordpress site settings to "Discourage search engines from indexing this site" and "Prevent third-party sharing for example.wordpress.com". The third party sharing setting prevents Wordpress from using your posts to train their AI models.

7. Update urls from old domain to new domain.

It's common practice to add a "CTA" or call to action at the end of a blog post. For me, that tends to be the related posts I've written in the past. Any links that contain the old domain need to be swapped to the new one.

For validating urls in my 100+ past posts, I also wrote a python script to help find broken links and .rst tags here: rst-url-validator Github Repo

Moving From Wordpress Was Easy With Pelican

I did thorough research into Python static site generators before choosing Pelican. The benefits of Pelican include a Wordpress import CLI that makes it easy to quickly compile an an alternative MVP to move your blog from Wordpress. Pelican is an obvious choice for static site generation in the Python ecosystem. After 17 months of using it, I can safely say I'm happy with the decision!

Supplementary Reading

Deploy a Hugo website to Cloudflare

How to Convert a Wordpress blog to an Astro Static Site

Pelican Documentation

Launching a Live Static Site Blog via Pelican, Github and Cloudflare Pages

When Microsoft Was Uncool and How They Flipped Apple

I began paying attention to what was relevant in the tech scene around 2014, in my 20s. Back then, I was just getting started studying Python. It was an interesting time in tech. The term "Big Data" was getting tossed around a lot, but the pandas library hadn't yet reached mass adoption in data circles like it now has today. People were still talking about Hadoop + Map Reduce. (RIP)

In the 2010s, it didn't take much perusing online to find people in the Python community bashing Microsoft. If tech companies were a high school, Apple was the cool kid everybody wanted to know, Microsoft was the kid who nobody liked and everyone made fun of. Understandably, the Windows operating system didn't mesh with Python programming as well as Linux or Mac OS. By 2024, Microsoft gained their mojo back, or found the mojo they never had. Having used Windows a lot at my last job, I recognize the OS and its Python implementation have flaws. I still got my work done and had no problems, without complaining. I continued to play around on Windows and write Python on it even though people trashed it online. I'm glad I did!

How did Microsoft flip Apple? Steve Ballmer left the company in 2014, yielding to Satya Nadella as CEO. Since then, the company culture shifted miraculously. In the Python community, they have made a huge impact by investing in the language. They constantly release free Python + AI courses, and integrated Excel with Python. Guido, the creator of Python is employed full-time, working on improving the Python language. That tells you a lot of how much has changed since Python's BDFL is still working there after 3 years. Microsoft's culture change propelled it into the 2020s with newfound momentum. With some timely bets, they saw the AI revolution coming and capitalized first.

If someone feels this way in 2024, they probably don't want to admit: Microsoft is Apple in 2012, and Apple is Microsoft in 2012.

What is funny to see is that nowadays, fewer people are bashing Microsoft. I used to see it regularly, people teeing off online, "writing Python on Windows is such a terrible experience for XYZ thing, why is Windows so awful??"" I see less of those people posting such thoughts now. Maybe they're still out there. If someone feels this way in 2024, they probably don't want to admit: Microsoft is Apple in 2012, and Apple is Microsoft in 2012. I posit they switched places in respective coolness among tech circles. People realized Apple is not the friend of developers or society in general. They are self-serving to a vicious degree. Apple is focused on maintaining their walled garden on iOS.

Microsoft is now a better advocate for techies and Python development. Sure, some people prefer to code on Macs, more power to them. Linux is typically the favorite of the three and it is awesome. It's also not released by a for profit corporation which is uber cool to developers.

Apple is also less cool due to their battle with Epic Games and insistence on 30% rake for in-app purchases on iOS. Not to mention an unwillingness to change their policies to appease stricter European Union regulations for things like 3rd party app stores.

Microsoft is integrating AI deep into their products. Apple, after being slow on the uptake to AI, followed Microsoft's lead to invest in OpenAI and roll its AI chat to iPhones. Who is the leader here? In terms of "What have you done for me lately?", it's Microsoft. In terms of who supports open and free information, it's Microsoft. Who's cool now?

Should You Block LLMs and Bots From Your Website?

Bots and robots.txt

Bots are crawling the internet. They make up a large chunk of all web traffic. On my blogs, I've always used a robots.txt to say to bots, "scrape away!" like this:

User-agent: *
Disallow:

Some websites use a robots.txt to say, "don't scrape our content". We can ask crawlers to restrict from crawling, but this is more or less an honor system. For this blog, I always welcomed all real traffic but activated Cloudflare's "Bot Fight Mode" to to protect against some of the bots.

Cloudflare Rolls Out New AI Blocker Configuration

I noticed in my Cloudflare Pages dashboard that a new setting has become available to me on the free plan.

This new configuration option gives another level of security if I want to use it. For now, I'm choosing to keep my blogs open to most traffic, including AIs while excluding some bots. Anyone who publishes to the internet needs to assess their willingness to trust big tech's bots to scrape their data.

You can block artificial intelligence (AI) bots, crawlers, and scrapers from scraping your website content and training large language models (LLM) to recreate it without your permission. When you enable this feature via a pre-configured managed rule, Cloudflare can detect and block AI bots from your website.

- AI Bots

If an LLM is just a remix of all the data it consumes, how do we trace the origin of its results? And how does that get attributed back? It seemed easier to tell with Google's link based search results. But even then, there was a lack of transparency. It's still that way today. I appreciate that Cloudflare is putting this choice to block or not block AIs in the hands of website makers. They're also releasing an "AI Audit" tool that lets website owners set a price for their sites with AI crawlers.

This murkiness leaves site owners with a hard decision to make. The value exchange is unclear. And site owners are at a disadvantage while they play catch up. Many sites allowed these AI crawlers to scan their content because these crawlers, for the most part, looked like "good" bots - only for the result to mean less traffic to their site as their content is repackaged in AI-written answers

...

We think that sites of any size should be fairly compensated for the use of their content. Cloudflare plans to launch a new component of our dashboard that goes beyond just blocking and analyzing crawls. Site owners will have the ability to set a price for their site, or sections of their site, and to then charge model providers based on their scans and the price you have set.

- Start auditing and controlling the AI models accessing your content, Cloudflare Blog

Crawler Traffic on lofipython.com, Aug. - Sept. 2024

The point of this post is to reflect on the nuances of web creators and their relationship with big tech. Everyone will have to decide for themselves if they think it's worth it. In this case, we're likely "paid in exposure". Big tech will distill the value of what we write to inquiring humans and reference back with links. Will website creators lose out on this exchange? Are we cutting off a channel of distribution if we push back against LLMs scooping up our data? Right now there seems to be more questions than answers. We'll see.

Disclosure: the author is a Cloudflare investor, going on 3 years holding shares.

Launching A Web Browser From Ubuntu Shell

Ubuntu allows aliasing commands to run a shell function. Below, I used xdg-open to open my Cloudflare Pages dashboard with an alias.

Add Ubuntu Function to .bashrc

I used VS Code to add this to my .bashrc file. Now, when I type cloudflare into my shell it launches the dashboard in Chrome. Remember to close and reopen a new shell before testing out the command.

1
2
3
4

open_cloudflare() {
    xdg-open "https://dash.cloudflare.com"
}
alias cloudflare=open_cloudflare

The pattern of being able to open a page in a web browser with a quick command could be applied to lots of my frequently visited websites.

Bonus Alternate Version: Python webbrowser Module CLI

Sometimes the right tool is Python. Other times, the Linux shell CLI tools are sufficient. Since this is a Python blog... here is a version that leverages the Python webbrowser module CLI that also works, assuming you're already in your Python environment.

1
2
3
4

open_cloudflare() {
    python -m webbrowser https://dash.cloudflare.com
}
alias cloudflare=open_cloudflare

Copying a Cell Value to a New Google Sheet With Apps Script

I've been messing around with Apps Script lately and am finding it useful. The below Apps Script was created with a few prompts to Bing Copilot. First, I asked Bing:

Write a Google sheets formula that copies the value in cell B10 to another sheet and also fills another column with today's date

...and a later prompt:

how can i make this script add a new row each time in the target sheet and appending to existing rows?

I refined with some followup prompts to write a script. It copies cell B10's value from a sheet then fills in a row in a different sheet with cell B10's value and today's date.

AI allows me to do things that would have taken more time to figure out before. For example, Bing just tapped the appendRow function to handle the updating of values without overwriting existing data. We don't need to know the specific Apps Script functions to write code in a new language anymore. AI can just fill in those details for us.

Apps Script to Copy a Cell Value and Create New Row with Today's Date

In this example, one sheet is named "Summary" and the other target sheet is named "Daily" with 2 column names.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

function copyTotalValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Summary");
  var targetSheet = ss.getSheetByName("Daily");

  // Get the value from B10 in Summary
  var value = sourceSheet.getRange("B10").getValue();

  // Get today's date
  var today = new Date();

  // Append a new row to the Daily sheet with the value and today's date
  targetSheet.appendRow([value, today]);
}

According to Bing, Apps Script is a JavaScript dialect:

Google Apps Script is based on JavaScript. It uses a subset of JavaScript and provides additional built-in functions...

Automate The Script From the Apps Script Jobs Dashboard

Go to the Apps Script Jobs dashboard.

Add a new script to run Apps Script.

Click the blue "Add Trigger" button to create a new job.

Supplementary Reading

Apps Script Reference Overview

Apps Script API

Apps Script Spreadsheet Service

Importing and Analyzing Data With Google Sheets Functions

This post focuses on Google Sheets formulas, rather than Python. If you're interested in accessing Google Sheets with Python, check out this post I wrote about the pygsheets library. I haven't personally tried it, but xlwings also looks like a decent option if you're looking for Google Sheets Python libraries.

Here are some functions I recently discovered to analyze data in Sheets. There a few different strategies you can use to import data into Google Sheets with the functions available. Below, you'll see some different approaches you use to get data into Google Sheets and then analyze it. The Google Sheets formulas rabbit hole is deep. Lots of power can be harnessed by getting familiar with the formulas it has built-in.

Year To Date SPARKLINE of a Stock

=SPARKLINE(GOOGLEFINANCE("Nasdaq:NVDA", "price", DATE(YEAR(TODAY()), 1, 1), TODAY(), "daily"), {"charttype", "line"; "linewidth", 2; "color", "green"})

Year to Date SPARKLINE of USD to MXN Currency Value with GOOGLEFINANCE + SPARKLINE USING IF to Dynamically Color the SPARKLINE

=SPARKLINE(GOOGLEFINANCE("CURRENCY:USDMXN", "price", TODAY()-365, TODAY(), "DAILY"), {"charttype", "line"; "linewidth", 2; "color", if(A2>0,"green","red")})

Above, we use an if condition to check another cell and set the color to green if > 0 or red if < 0.

Market Cap with GOOOGLEFINANCE

=GOOGLEFINANCE("Nasdaq:NVDA", "marketcap")

Price/Earnings Ratio

=GOOGLEFINANCE("Nasdaq:MSFT", "pe")

Daily % Change of a Stock

=GOOGLEFINANCE("Nasdaq:TSLA","changepct") &"%"

Import the Price of ADA Cryptocurrency with IMPORTDATA

=IMPORTDATA("https://cryptoprices.cc/ADA")

This function imports the price of Cardano cryptocurrency from cryptoprices.cc.

Import the Market Cap of ADA Cryptocurrency with IMPORTDATA

=IMPORTDATA("https://cryptoprices.cc/ADA/MCAP")

This function imports the current market cap of Cardano cryptocurrency.

Import the Daily % Change of Ethereum Cryptocurrency with IMPORTXML and INDEX

=TEXT(
 IF(
     IMPORTXML("https://coinmarketcap.com/currencies/ethereum/", "//p/@data-change") = "down",
     "-" & INDEX(IMPORTXML("https://coinmarketcap.com/currencies/ethereum/", "//p[@data-change]"), 1, 2),
     INDEX(IMPORTXML("https://coinmarketcap.com/currencies/ethereum/", "//p[@data-change]"), 1, 2)
 ),
 "0.0%"
)

This method uses IMPORTXML to import data to Google Sheets by passing an "XPath query". The first line checks if the direction of the % change is "down". If it is down, then we know the % change is negative, otherwise the % change is positive. To select the HTML element, I right-clicked the number on the page that I wanted to import on coinmarketcap and selected "Inspect" to reference the HTML class names for the paragraph I was targeting.

Conditionally sum a range with SUMIFS, SUMIF and COUNTIF

=SUMIFS('sheet_name'!G:G, 'sheet_name'!N:N, ">0")

In the following examples, "sheet_name" = Your Google sheet's name. This function sums the corresponding cells in column N if column G contains a number greater than 0.

=SUMIF('sheet_name'!N:N, "Some Value",'sheet_name'!G:G)

This function sums all values in column G if the values in column N are equal to "Some Value".

=COUNTIF('sheet_name'!A:A, "Some Value")

Count all the cells in column A that equal "Some Value".

Select Columns from a Dataset

{ 'sheet_name'!A:D, 'sheet_name'!T:X, 'sheet_name'!Z:AA}

Google Sheets recognizes this format of bracket enclosed ranges of columns to select into your dataset. In the next example, you can see this applied.

Conditionally Select a Range of Cells from a Dataset with SORTN, FILTER, and REGEXMATCH

=SORTN(FILTER({'sheet_name'!A:A, 'sheet_name'!E:E, 'sheet_name'!P:P/1,'sheet_name'!F:F}, REGEXMATCH('sheet_name'!P:P, "-")), 5, 0, 3, TRUE)

This formula constructs a dataset, then filters it on a condition using REGEXMATCH to check if the cell contains a hyphen (-). 5 specifies how many rows to return, and 3 specifies which column to sort on, in this case the 3rd column.

Conditionally Select a Range of Cells from a Dataset with SORTN and QUERY with SQL-Like Language

=SORTN(QUERY({'sheet_name'!A:A, 'sheet_name'!E:E, 'sheet_name'!P:P,'sheet_name'!F:F}, "SELECT * WHERE Col3 IS NOT NULL"), 6, 0, 3, FALSE)

This queries rows that are not null, containing data.

Official Function Documentation

Google Sheets Function List

GOOGLEFINANCE

IMPORTDATA

IMPORTXML

SPARKLINE

FILTER

SUMIFS

QUERY

SORTN

INDEX

REGEXMATCH

There is Background Noise in Real Life

While walking down a busy avenue in Mexico, I listened to a conversation in Spanish. I'm not fluent in Spanish, but I have 4 years of high school classes and ~2 more years of real world experience. Spanish is not spoke in a bubble. It needs to be practiced to be learned. I put much effort into listening. There is much more I have to learn. Typically, I try to compensate for some lack of skills with intense focus. Sometimes I know what is said and more often I am closer to guessing.

On this walk down the street, I attempted to listen to my friends speak. In the back of my mind, I was frustrated because the noise of the cars going past us and a city in motion made it near impossible to hear the words. Similarly, attempting to listen in a bar, party or anywhere playing music is very tough.

I used to think, "if only it was more quiet!" while walking and straining to hear what my friends said over the noise. Today I had an epiphany: there's always background noise.

A difference between textbooks and real life: there's usually noise in the background.

We should embrace practicing and growing our skills because there will always be "noise". This is a tidy metaphor for distraction and our focus bandwidth. In the lens of coding, the "noise" might be skill deficiencies and lack of understanding, other co-workers, unimportant projects, meetings, social media, games and anything that misdirects us away from our primary goals.

It's easy to get annoyed when there is excessive noise. It makes it way harder to hear what is being said. Nonetheless, I don't have to let the noise distract my effort and intention to learn. So it is with programming and all skills. There will always be "noise". Embrace the noise and lean heavily on your intention to practice.

Image Generated with Bing Image Creator