How to use AI in Excel with Analyze Data

Microsoft now provides a way to analyze Excel spreadsheets and data using AI to find insights, trends, sort, create graphics, and charts without uploading your files to ChatGPT or Bing. This feature only appears to be available in both Destkop and Web versions of Enterprise editions of M365/Office365. For example this feature is not available in the free/hotmail/Microsoft account version of Excel for Web.

Here is Microsoft’s link for how to use Analyze Data.

If you’d like to use a sample/example spreadsheet go ahead and download the one here. You may need to upload the file into OneDrive before you can use it. Also be aware when opening the file in the web-version of Excel you may need to allow pop-ups in Edge or Chrome.

To use the feature, Click the HOME tab, and then on the far-right of the ribbon, click Analyze Data:

We can now ask questions about our data or let it provide us with it’s own insights. For example we can pose the question, “List Full Name and Salary if Job Title is Vice President.” And as simple as that it generates a pivot table that we can “Insert” (which means create a new “Sheet” at the bottom of the page.)

Note that the module provides a different prompt which is more succinct (‘Full Name’ and individual ‘Annual Salary’ for ‘Job Title’ ‘Vice President’) than the one we wrote at the bottom of the module:

When we click Insert Pivot Table, a new Sheet appears at the bottom of our Workbook and names the workbook “Suggestion 1, Suggestion 2” etc. :

Excel’s Analyze Data feature can provide many insights into your data, but it’s always a good idea to double-check the results.

A great starting prompt would be something like, “What are some insights in this data?” For our sample Excel spreasheet, it returns charts for ‘Bonus %’ by ‘Department, and ‘Annual Salary’ by ‘Gender’ and ‘Country’. Also, it will find things like ‘Job title’ Vice President and Director have noticeably higher ‘Bonus %‘.

I hope this information was helpful, and let us know in the comments if you have found other ways to analyze Excel data using AI.

ChatGPT produced Bash shell script to display stock tickers every 10 seconds

Because I have a putty terminal running on my PC most every day (mostly sitting there doing nothing, waiting for a command,) I thought I’d put that open terminal to use and have it display stock tickers. So I asked ChatGPT to create a bash shell script that display a ticker and it’s price from the internet. I am not a good programmer/developer, and I can barely write a few scripts, but by using ChatGPT I was able to produce what I wanted in a short amount of time. Below is the script that it produced:

#!/usr/bin/env bash
set -e

LANG=C
LC_NUMERIC=C

SYMBOLS=("$@")

if ! $(type jq > /dev/null 2>&1); then
  echo "'jq' is not in the PATH. (See: https://stedolan.github.io/jq/)"
  exit 1
fi

if [ -z "$SYMBOLS" ]; then
  echo "Usage: ./ticker.sh AAPL MSFT GOOG BTC-USD"
  exit
fi

FIELDS=(symbol marketState regularMarketPrice regularMarketChange regularMarketChangePercent \
  preMarketPrice preMarketChange preMarketChangePercent postMarketPrice postMarketChange postMarketChangePercent)
API_ENDPOINT="https://query1.finance.yahoo.com/v6/finance/quote?lang=en-US&region=US&corsDomain=finance.yahoo.com"

if [ -z "$NO_COLOR" ]; then
  : "${COLOR_BOLD:=\e[1;37m}"
  : "${COLOR_GREEN:=\e[32m}"
  : "${COLOR_RED:=\e[31m}"
  : "${COLOR_RESET:=\e[00m}"
fi

symbols=$(IFS=,; echo "${SYMBOLS[*]}")
fields=$(IFS=,; echo "${FIELDS[*]}")

results=$(curl --silent "$API_ENDPOINT&fields=$fields&symbols=$symbols" \
  | jq '.quoteResponse .result')

query () {
  echo $results | jq -r ".[] | select(.symbol == \"$1\") | .$2"
}

for symbol in $(IFS=' '; echo "${SYMBOLS[*]}" | tr '[:lower:]' '[:upper:]'); do
  marketState="$(query $symbol 'marketState')"

  if [ -z $marketState ]; then
    printf 'No results for symbol "%s"\n' $symbol
    continue
  fi

  preMarketChange="$(query $symbol 'preMarketChange')"
  postMarketChange="$(query $symbol 'postMarketChange')"

  if [ $marketState == "PRE" ] \
    && [ $preMarketChange != "0" ] \
    && [ $preMarketChange != "null" ]; then
    nonRegularMarketSign='*'
    price=$(query $symbol 'preMarketPrice')
    diff=$preMarketChange
    percent=$(query $symbol 'preMarketChangePercent')
  elif [ $marketState != "REGULAR" ] \
    && [ $postMarketChange != "0" ] \
    && [ $postMarketChange != "null" ]; then
    nonRegularMarketSign='*'
    price=$(query $symbol 'postMarketPrice')
    diff=$postMarketChange
    percent=$(query $symbol 'postMarketChangePercent')
  else
    nonRegularMarketSign=''
    price=$(query $symbol 'regularMarketPrice')
    diff=$(query $symbol 'regularMarketChange')
    percent=$(query $symbol 'regularMarketChangePercent')
  fi

  if [ "$diff" == "0" ] || [ "$diff" == "0.0" ]; then
    color=
  elif ( echo "$diff" | grep -q ^- ); then
    color=$COLOR_RED
  else
    color=$COLOR_GREEN
  fi

  if [ "$price" != "null" ]; then
    printf "%-10s$COLOR_BOLD%8.2f$COLOR_RESET" $symbol $price
    printf "$color%10.2f%12s$COLOR_RESET" $diff $(printf "(%.2f%%)" $percent)
    printf " %s\n" "$nonRegularMarketSign"
  fi
done

This produced the following, for example:

This works well, however I want the script to display a number of different tickers and have the data refresh every 10 seconds. The next script produced is the following ticker_loop.sh. I added my tickers

#!/bin/bash

while true; do
    clear
    ./ticker.sh PLTR SOFI HIMS ARKK AHT QYLD MSFT ONON
    sleep 10
done

This is what I was looking for! So I simply had to modify both to run as executible / binary files. To do so, use the command:

chmod +x ticker_loop.sh

Lastly, run the script with the command ./ticker_loop.sh

Bash shell script to query a domain name using dig without the any flag

How do I get all of the DNS records for a domain using the dig command in only “Answer Section” (+answer) format? The command should return A, MX, NS, TXT, SOA and CNAME records.

Normally using the “any” flag, we would get all of this information at once, however, when attempting to run a dig command with the ‘any’ switch, we do not get the DNS records we want:

dig jasoncoltrin.com any

The above command returns an answer section with only: “RFC8428” “”

According to chatgpt, this means that the ‘any’ query type is not guaranteed to return all the records for a given name, and some DNS servers may choose to return an empty answer instead. This is done to improve the performance and security of the DNS system.

Still, I want to have a single command to get the most information at once, and the following command does so, however, writing the command is impractical:

dig +noall +answer +multi jasoncoltrin.com A jasoncoltrin.com MX jasoncoltrin.com NS jasoncoltrin.com TXT jasoncoltrin.com SOA jasoncoltrin.com CNAME

I also tried the following with no luck:

dig +noall +answer jasoncoltrin.com A,MX,NS,TXT

This only returned the A records.

So instead, we can use a bash script to create a $domain variable, and have the script use the ‘read’ command to prompt us for the domain name:

#!/bin/bash

read -p "Enter the domain name: " domain

dig +noall +answer +multi $domain A $domain MX $domain NS $domain TXT $domain SOA $domain CNAME

To write the script, do the following:

vi digdomain.sh

(insert) > copy/paste script > (Escape) > :wq

Then make the script executable with the command:

chmod +x digdomain.sh

Run the command using the ./ prefix:

./digdomain.sh

When we run the script, we’re prompted for the domain name, then the result is most of the information we want in an easy-to-read format:

jason@ubuntu0:~$ ./digdomain.sh
Enter the domain name: jasoncoltrin.com
jasoncoltrin.com.       118 IN A 172.67.196.181
jasoncoltrin.com.       118 IN A 104.21.44.69
jasoncoltrin.com.       1854 IN MX 10 mailstore1.secureserver.net.
jasoncoltrin.com.       1854 IN MX 0 smtp.secureserver.net.
jasoncoltrin.com.       5652 IN NS daisy.ns.cloudflare.com.
jasoncoltrin.com.       5652 IN NS lee.ns.cloudflare.com.
jasoncoltrin.com.       300 IN TXT "Currently located in a black hole\" \"Likely to be eaten by a grue"
jasoncoltrin.com.       300 IN TXT "google-site-verification=key"
jasoncoltrin.com.       300 IN TXT "google-site-verification=key"
jasoncoltrin.com.       2052 IN SOA daisy.ns.cloudflare.com. dns.cloudflare.com. (
                                2305113011 ; serial
                                10000      ; refresh (2 hours 46 minutes 40 seconds)
                                2400       ; retry (40 minutes)
                                604800     ; expire (1 week)
                                3600       ; minimum (1 hour)
                                )

This made me happy because I had forgotten about my easter egg TXT record. 🙂

How to set Windows Date and Time formats to the internationally recognized ISO 8601 standard

YYYY-MM-DD is the standardized international date formatting scheme as codified in ISO 8601. The standard aims to provide a well-defined unambiguous method of representing calendar dates and times in worldwide communications.

The standard has the additional benefit of organizing file and folders in a data-system in an intuitive ascending or descending order. Additionally not only is this good for Excel sorting, it’s also excellent for file naming as it sorts properly (based on date) even if the sort is done alphabetically and not numerically.

Also, the ISO 8601 is a format recognized internationally, and will prevent costly mistakes (the refund expires on 2/22/21,) and inconveniences (my plane arrives in Japan at 5:30P on 12-23-22.)

Question: Yes this is all fine and good but I can’t remember all this, how do I get used to the correct date format?

Answer: Change the Short Date format under Windows Settings > Region Settings or Date and Time Format.

  1. Click on the Windows Start button and type Region
  2. Click on Region Settings or Change the Date and Time Format
  3. In the Date and Time Format settings, change the Short Date format to yyyy-MM-dd
Windows Region Time Date Format Settings YYYY-MM-DD

4. Additionally we can change the Short Time format to hh:mm tt (which adheres to the ISO 8601 format)

5. Now we can see in the bottom-right corner of our screen, our date and time formats are displayed:

Systray Time Date Format

ISO 8601 Filename conventions

Naming files using the ISO 8601 scheme can have many variations, however the following makes most sense to me.

The timestamp is only a portion of the filename, it should not dictate how the other portions are formatted.

Typically dashes are used to separate fragments of a filename, while underscores delimit tokens within a fragment.

As such, the timestamp and the filename should be delimited by dashes, as they are different sections. Keep underscores within a section.

Then you get filenames which look like YYYY-MM-DD-file_name-v3.41. Which is clean and consistent (and easily copy / paste-able.)

Which of these filenames below is going to have you pulling your hair out, and which will leave you nice and happy when sorting by date in Windows File Explorer?

Loan – Spreadsheet v 1.1 May 4, 2020.xlsx

Loan – Spreadsheet v 1.1 May 2, 2020.xlsx

Loan – Spreadsheet v 1.3 January 1, 2020.xlsx

Loan – Spreadsheet v 1.2 May 2, 2020.xlsx

Loan – Spreadsheet v 1.2 May 4, 2020.xlsx

or

2020-05-04-Loan_Spreadsheet-v.1.1.xlsx

2020-05-02-Loan_Spreadsheet-v.1.1.xlsx

2020-01-01-Loan_Spreadsheet-v.1.3.xlsx

2020-05-02-Loan_Spreadsheet-v.1.2.xlsx

2020-05-04-Loan_Spreadsheet-v.1.2.xlsx

You guessed it – ISO 8601!

How to move a partition to the end of a disk with free MiniTool Partition utility

Recently after cloning a system using Clonezilla, I found all the empty space on the drive was located on the end of the disk. The System Recovery partition was between the empty space and the c: drive. We want the primary partition to extend and include the empty space, but the Disk Management utility built into windows cannot do this for us. First I tried a gparted open-source utility but the live USB failed to load and see my system drive. So I did a little looking and found a utility made by MiniTool – MiniTool Partition Wizard Free will do exactly what we need to do. The good thing about MiniTool’s utility is we can run and perform our partition operations inside of a running Windows 10 system.

How to move and extend partitions using MiniTool’s Partition Wizard Free utility

When we look at our Windows Disk Management, we see our empty space is on the wrong side of our Recovery Partition:

Windows Disk Management move Unallocated Free Space

We want the 509 MB Recovery partition to live to the “right” of our 65GB of empty/unallocated partition. Once we have the 65GB Unallocated partition next to our C: drive so we can then extend/resize the C: drive to use the empty space.

  1. Make a backup of important data. Because this is a fresh install/clone, I’ll skip this part.
  2. Install MiniTool Partition Wizard Free (during install, I confirmed I only want to install the free edition, and chose not to install the “Shadow” backup application.)
  3. Once opened, select the Recovery Partition we want to move, and then click Move/Resize
Select Partition to Move

4. Next slide the (whole) partition to the far right of the partition bar (screenshot it before the operation).

Move Partition Before

5. The result should look like the following screenshot after “sliding” the partition to the far right location.

Move Partition After

6. Click the Apply button

Apply Move Partition

7. If we close the MiniTool Partition Utility and check Disk Management, we can see our Recovery Partition is now at the end of the disk, and free space is adjacent to our C: drive.

Disk Management after Move

8. To extend the C: drive and use the unallocated/free partition, we can use either Disk Management or the MiniTool Partition utility, but here we will continue to use the free utility.

Open MiniTool’s Partition Utility again and select the C: drive partition. Then click the Move/Resize button again. Move the right-most “Handle” and slide it to the far right. Below is the “Before and after” screenshots:

Resize C: drive Before
Resize C: drive After

9. Remember to click Apply after completing the operation.

We’re done! We can close the MiniTool utility and Disk Management and then open Disk Management again (or refresh the disks) and we can see our C: drive is now extended and using our free disk space.

Disk Management after Resize Extend

How to fix 550 5.7.520 Access denied, Your organization does not allow external forwarding.

If our organization controls two office365 tenants, at some point we may wish to enable forwarding of email from an address hosted in one of our tenants to an address hosted in another. When we enable the forward in O365 Exchange Admin, the end user may complain that every time they try to send a message to see if it is forwarded, they receive the following bounce back message:

Remote Server returned '550 5.7.520 Access denied, Your organization does not allow external forwarding. Please contact your administrator for further assistance. AS(7555)'

By default, Microsoft 365 Defender sets up an Anti-Spam outbound policy. And the policy default sets Automatic Forwarding to “Automatic: System Controlled.” Since we do not want to modify this default policy, instead we can create a policy (with a higher priority) that defines certain users or groups to allow forwarding.

  1. Go to www.office.com and log into the tenant which hosts the email address that we want to forward mail from. (do not log into the destination email address tenant)
  2. Open the Admin Center
  3. Next, click Show All (admin centers) and then click Security.

5. Next, in the Security / Microsoft 365 Defender Admin Center, under Email & Collaboration, click on Policies & rules.

6. Here, click on Threat Policies

7. Under Threat policies, click Anti-Spam.

8. Under the Default Anti-Spam outbound policy (Default) we will probably find Automatic Forwarding is set to Automatic – System-Controlled

9. Close the Default Policy and then at the top of the screen click the + Create Policy drop-down and choose Outbound

10. In the new Outbound policy, edit the description to something like “Custom Outbound Mail Forward“, and add the Users or Groups to the policy (whom you want to give the ability to forward.)

11. At the bottom of the new custom policy change Automatic Forwarding to: On – Forwarding is enabled

12. Save and close the new policy and that should do it. Try sending some test messages to see if the forward works correctly. We may need to change the new policy’s Priority to 0 if something still isn’t working. Also, don’t forget to double check the Automatic Forwarding on the mailbox itself.

New World – How to Find Parsley to Craft Exotic Herb Supplies

We may get a quest to Craft Exotic Herb Supplies from a Town Board quest or from an NPC. When looking for Exotic Herbs such as Parsley, the herbs will not have a different look than other herbs, instead they will be purple and labeled “Herbs” when harvesting. They look similar to Lavender IRL.

When harvesting the Parsley from Herbs, we have about a 1-in-10 chance of finding a Parsley. Often times, in addition to obtaining Parsley, we’ll also harvest Peppercorns, Hyssop and Cinnamon.

Where to Find Parsley in New World

So finding Parsley is easy enough if we know where to look and what it looks like. In this case, Parsley is found in the far South West of the map, in tropical Cutlass Keys. I found the most near BALMY VELDT and HAUNTED ISLE.

Getting there, expect to encounter Tropical Boars, and Crocodiles. I am level 33 at the moment and the enemies are up to level 35. Try to stick to the roads and if too many mobs are after you, it’s best to run and dodge!

I hope this guide helps you find Exotic Herbs in New World.

New World Briar Branches – what they look like and where to find them

New World Briar

When trying to complete the Windsward Hamlet Barkeep’s No Confidence Stew quest, we need to find Briar Branches.

You can find Briar Branches near rivers and lakes. They are brown thorny bushes with green leaves, and will display “Briar” when looking at them. I found them near a lake just South West of Windsward Hamlet.

New World – How to Find Sporebloom Fruit to craft Common Health Potion

So there is a quest named “Alchemists Needed” in WindsWard Hamlet. The mission is to Acquire Common Health Potion. In order to complete this mission, one of the ingredients for the potion is named Sporebloom Fruit. Searching resource maps online didn’t help so I basically ran around along all the rivers in the area and collected all types of plants. I was then able to craft the Common Health Potion. At this point I found that we can click on the Recipe ingredient titled:
Tier 2 Medicinal Reagents (PRIMARY)

Under this recipe item I finally found all of the ingredients that make up Sporebloom Fruit. 1. Click on the Common Health Potion 2. Click on the Tier 2 Medicinal Reagents (PRIMARY) 0/1 Sporebloom Fruit menu:

3. Now uncheck / clear the box that says Owned Items:

4. Hover your mouse over each ingredient. Now we can see what the (Primary) ingredient constitutes, and they are:

  • Sporebloom Fruit (Sporebloom Fungus)
  • Glowing Mushroom Cap (Any Fungi)
  • Gillflower Gills (Gillflower Fungus)
  • Platecap Flesh (Platecap Fungus)
  • Lifebloom Stem (Lifebloom Plant)