Your way out from an Excel sheet💩 job into your nice holiday times 🌞
Package Manager (recommended) 📦
To be able to easily install and remove software on your computer. Not strictly essential, but you usually need a package manager. Linux came first with this idea, but here is how you can install others in Windows (winget or choco, but I’d stick with winget) and Mac also (homebrew).
- macOs: https://brew.sh/
- win: https://learn.microsoft.com/en-us/windows/package-manager/winget/
- Linux: you are nerd enough to already know all this, but yes is
aptorapk.
time to complete: 5 minutes.
Install Dotnet 🌻
- without a terminal, if you didn’t install a package manager: here.
- macOS:
brew install dotnet - win:
winget install Microsoft.DotNet.SDK.8 - Linux:
apt install dotnetor variants forapk.
Then test all is fine and working in a new terminal window, with:
> dotnet --versiontime to complete: 10 seconds.
Terminal (PS, optional but also awesome) 🐠
The most basic tool in programming, dating back to the 60s, is the terminal. Sounds strange but even nowadays is still in use, not as much, but can always come in handy. To have a common experience on all operating systems, you could install, is a great terminal that works the same across operating systems (Win, macOS, Linux).
Now that you have dotnet, you can install it in one shot!
> dotnet tool install --global PowerShelland you can test from your terminal, it with
> ps --versiontime to complete: 30 seconds.
Editor 😺
The first thing you need to code in any programming language is a code editor that helps you, it’s kind of like Word or Docs, just so this is what you can use no matter what operating system you are running, use Visual Studio code, it supports tons of languages, the most widely used editor in general and it’s free to use and awesome.
You can easily install it with your package manager, that’s the recommended way over the manual installation process.
- macOS:
brew install vscode - win:
winget install Microsoft.VisualStudioCode - Linux: https://code.visualstudio.com/docs/setup/linux
Once you can open and check VsCode out, there is plenty of tutorial for its basic usage, but now you should go to the extensions button, and install Ionide or clicking here.
This extension is required to use F# Programming Language, a wonderful and easy-to-use programming language, in my opinion, the best to get you started in dotnet (an ecosystem of languages). See at the end for why F#.
time to complete: 5 minutes.
A Blank Sheet 🐉
When you start programming you create a script file with the extension .fsx, that’s like a new blank sheet. Nothing has been filled in yet. you can use the terminal or just do it directly from the code editor (vscode) or in any other way you prefer.
> touch myscript.fsxtime to complete: 30 seconds.
A “Temp” Sheet (the REPL) ☯️
F# also supports interactive evaluation, so our “sheet file” can also be just a runtime sketch, where we do not save any file yet! a bit like if we had a temp Excel sheet or a temporary table in a database, by entering in REPL mode (like Python). A bit like playing around without ever pressing the save button, this can be useful for testing ideas quickly.
The REPL mode is achieved by running this command on the terminal, without arguments.
> dotnet fsitime to complete: 30 seconds.
A Cell 👮
the smallest thing you can do is assign a value to a variable, that’s exactly like a CELL in a sheet.
let a = 1
> a;;the special it cell in the REPL contains the last result of your evaluation:
> it;;time to complete: 30 seconds.
Sending Commands in the console 🚨
An expression can be sent to the REPL by selecting it all in your code editor (vscode) and then hitting alt/option + enter. You can also always type full expressions in the REPL as well if you prefer, e.g.
> 1 + 1;;
> let x = "hello";;Running the SHEET!🏃♀️
In Excel sheets and the like we have no concept of running a sheet, because, well, what you see is what you get. But, in programming, we do. The reason and motivation behind this is that most of the time, we don’t plug all the values in our program beforehand, but we use script arguments!!!! We will see later, for now, trust me.
In your editor (vscode + Ionide) you can usually just press the green play ▶️ triangular button at the corner of your script, but you can also do the same thing from your terminal:
> dotnet fsi myScript.fsx;;Printing Values! 📝
When your script executes, the last value in the script will be the result, but if you want, you can still print values in the middle of you script, or wherever you deem useful, with the handy printfn function
printfn $"some value {someVariable}"REMEMBER ;; in the command line ⚡️
IMPORTANT: ALWAYS use double semicolumns ;; to SEND the command before hitting enter in the F# CLI (REPL), this is because the REPL thinks you want to write more code in the new line or the following lines below if you don’t do that. unless you want to do that of course.
> for i in [1 .. 10 ] do printfn "REMEMBER TO USE SEMICOLUMNS";;A Function (of 2 Cells) 🎄
To sum 2 cells together, in Excel, we can use the SUM function, taking the reference of two other cells, and summing them in another cell as a result.
What we do in functional programming is similar, we bind an expression to a symbol, before we have seen how to bind a single value to a single cell, here is how to bind a function that works on other values, we can call this an expression binding, the cell value of our sumResult cell depends on the individual cell values of a and b.
let a = 1
let b = 2
let sumResult = a + b
printfn $"sum of {a} and {b} is {sumResult}"
> sumResult;;let’s define a custom function now, to make it simple we can just define our version sum function
let sum a b = a + b
> sum 10 20;;
> sum 1 2;;
> sum -100 +100;;time to complete: 5 minutes.
Anonymous Single Cell functions 🎅
as an alternative way to create custom functions, we can also use the so-called lambda or arrow functions, they are useful as they can be defined inline, inside other functions as we will see shortly.
let sum = fun a b -> a + b
> sum 1 2;;A Row ⛵️
A row in programming is often called a list, collection, sequence, or array. The concept is similar, we have different implementations and performance considerations, but for our use case we can say that a list is sufficient, this is how you declare a list in F#.
In F# semicolumns are optional if you format some entity vertically, as indentation is enough. This is true in general, not only for lists.
let myRow = [ 1; 2; 3 ]
let myRowInVertical =
[
1
2
3
]time to complete: 5 minutes.
A Row function (Cell by Cell, pull down) 🍰
The most common example of mapping function is the map function, unsurprisingly, available in all collection modules, usually Seq is the most general module as all collections implement sequence, so it’s good idea to use it in general. but there might be use cases where you want to use a specific module instead, like List or Array.
> [ 1; 2; 3 ] |> Seq.map (fun a -> a + 1);;time to complete: 5 minutes.
Filtering 🚭
In F# we can directly filter our data with a single function, a filter in the end is, similarly to map, just a function which takes a boolean condition (predicate) anonymous function instead of a mapping anonymous function (mapper or selector):
> [ 1; 2; 3 ] |> Seq.filter (fun a -> a > 1);;A Rows function (aggregation) 🚵♀
In Excel we have aggregation functions, that given a set of rows or also on multiple columns, can return a single Cell value. in F# similarly, we have aggregation functions over collections, that return an unique value as result.
> [ 1; 2; 3 ] |> Seq.sum;;A custom aggregation function can also be defined inline, as a anonymous function using the fun keyword, a common way to define custom generic functions on aggregation is using the fold higher order function.
[ 1; 2; 3 ] |> Seq.fold (fun a n -> a + n + 1) 0 ;;What we have seen now with Seq.map, Seq.filter, Seq.fold: functions taking other functions or anonymous functions as parameters/arguments, are called higher order functions or HOFs, so don’t be afraid if someone uses the term now! you are becoming an expert already!
time to complete: 5 minutes.
Composing Row Functions (|>) 🚜
In F#, Elixir, and not so many other programming languages is very easy and nice, more than in more common languages like python or javascript, to compose row functions or functions on collections of elements, using the wonderful pipe forward operator |>.
[1 ; 2; 3]
|> Seq.map (fun x -> x + 1)
|> Seq.map (fun x -> x * 2)
|> Seq.filter (fun x -> x > 4)
|> Seq.sum;;Special Custom Row Functions 👽
In Excel we also have scripts behind the scenes (usually VBScript but recently also python), to define our custom functions for the most complex scenarios, some similar functionality is given in F# by computation expressions. Some nice computation expressions are already defined for us so that all collection and sequence modules already include list comprehensions computation expressions, like in Python but even more powerful.
let infiniteSequence =
let mutable i = 0
seq {
while true do
i <- i + 1
yield i
}
> infiniteSequence |> Seq.take(3);;
> infiniteSequence |> Seq.take(100);;In this example, we created an infinite (because of the while loop) lazy sequence seq, a special kind of lazy collection which can generate its items on demand.
Having Arguments with Your Script! 🐯
Using script arguments is like being able to dynamically assign your input cells with fresh input data whenever it becomes available or you feel like, in 1 go!
What if I don’t want the initial value for some of my cells as: 1, or 2, but I want to decide that later in an easy way. Maybe I want someone else to decide it?
Maybe I want to share my sheet/program with some friend or colleague, and they have to put their own parameters in!
let args = fsi.CommandLineArgs
for arg in args do
printfn $"{arg}"
> dotnet fsi myScript.fsx "hello";;MAGIC! ⭐️ 🐰
A bit like when some shared sheet gets updated by some other colleague online or by some program, but you can decide any time what the INPUTS of your sheet are going to be!
This is the concept of script parameters. Your magic sheet can also perform I/O (input/output) operations, meaning, talking to your PC, the network, or other connected devices or users! You are becoming a real hacker! we will see how to do some IO later!
Requesting User Inputs 🐧
in F#, and in many programming languages, requesting a user input from the terminal, is as easy as a simple instruction
> System.Console.ReadLine();;Template Sheets (packages) 📊
Very important in programming is to not copy and paste by hand at all times, even though sometimes we do. In general, instead, we use a much smarter way of doing it, called software modules or packages.
A bit like templates for sheets.
You can create your own and you can also import packages made by other people, which is what you will be mostly doing, 99% guaranteed. This is similar to Python pip install or javascript / nodejs npm install.
Here an example of a polyglot library (available in many programming languages) that allows you to quickly plot diagrams!
#r "nuget: XPlot.Plotly"
open XPlot.Plotly
[ 1 .. 10 ] |> Chart.Line |> Chart.Show
And you can find tons of packages on Nuget, for .NET apps, or just search in Google appending the Nuget keyword to your search, depending on your needs, or you can install extensions to search for packages in your editor.
Formatting Cells (Value Types) 💴
In excel we can allow input validation by formatting cells. It is very useful in several ways, because we can prevent erroneous values from entering our cells. If we know something is Euros, we don’t want to allow strings to be entered, if we know something is a phone number, we do not want to allow names or strings to be entered in. If we enter emails, would be nice to be able to validate only valid emails.
some common format types (value types) are : int, long, float, double, decimal for numbers, string and charfor text, DateTime or DateOnlyand their combinations as collections…or rows.
Tables (and Custom Formatting) 🏰
Columns are great, but what if you could shape things in such a way that they would be easier to explain to colleagues or friends, and to validate their inputs in a consistent, shareable and unified way. Wecolme the custom formatting of complex data types, of records and unions.
Imagine a shop, having orders, and having one column for each property of the order, like ordernumber, items, andprice . In F# and other languages we have ways to enforce special conditions on special business tables (records), so that the data entered in them cannot be wrong, and always with their columns (properties or fields) in the right format!
type Amount = { Value: decimal ; Currency: string }
type Order = { OrderNumber: string ; Items: int; Price: Amount }
let orderOne = {
OrderNumber = "ABCDE0001"
Items = 10
Price = {
Value = 200.0m
Currency="EUR"
}
}
// try create orderTwo and orderThreeTables with Variations (Unions) ⚔️
Now imagine having 3 types of orders, placed orders, shipped orders, and delivered orders. Only shipped and delireved orders have tracking ids, wereas sold orders don’t have any tracking yet. In Excel we can usually express this with extra columns to mark the order as shipped or delivered, and maybe mark an optional column with a NULL or N/A cell? but maybe there is a better way. Maybe they actually different entities, they are evolving in time, depending on the context and customer and business actions on them, and depending on it they become different things with different properties (like a tracking number).
Welcome Discriminated Unions to the rescue, we can eliminate our boolean yes or no columns.
type Amount = { Value: decimal ; Currency: string }
type Order = { OrderNumber: string ; Items: int; Price: Amount }
type TrackingNumber = | Tracking of string
type SalesPrice = | SoldFor of Amount
type BusinessOrder =
| Placed of Order * SalesPrice
| Shipped of Order * SalesPrice * TrackingNumber
| Delivered of Order * SalesPrice * TrackingNumberMore information on discriminated unions and how they work here.
Now you can start with what you have and explore the resto of the world of programming. Have fun!
After thoughts: why is F# easy to learn? 🍾
In the .net language family, is the language closest to many other commonly used ones like python or javascript, much easier to learn than C# or Java. You don’t need to learn object-oriented programming right away, but you can just use “procedural” (but also functional) programming to get started, but without hidden dangers (mutation is allowed, but explicit!).
Functional programming in F# is much similar to:
- Excel or Google Sheets
- SQL and declarative programming
- Everyday Math
- optimization reasoning/business reasoning, useful to solve real-world problems quickly! collections of items, pipes (|>), transformations, inputs and outputs.
- python is the easiest and most widely used programming language, because it uses space indentation (not curls), has a REPL and can be used from single script files!
- javascript, the language of web browsers and frontend development: because you do not need to declare your types upfront, easier than typescript!
- great support for functions and operations on sets/collections of items, in a very easy way, like Excel, python, or javascript.
