Keep Up With Your Cold Storage Portfolio In 2 Clicks

Time to read: 8 minutes
Time to create solution: 25 to 60 minutes

I have created a Powershell script that can be ran on any windows PC with Excel to keep up with your portfolio. This is most beneficial for people looking for a solution to visualize their portfolios on cold storage, since the most popular hot wallets already have this functionality built-in.

Requirements:

You are using a Windows PC
You have Microsoft Excel
Slight familiarity with Excel
Ability to edit 2 lines of code with your coins of choice and file path

Results:

With a little set-up, the powershell script will automatically populate and calculate the useful information to keep up with your portfolio and see the most current snapshot of its value.

Header Values:

Coin: the coin ticker symbol
Name: its name
BTCValue: its current BTC value per coin
Amount: The amount you own
BTC: The amount of BTC the coins you own are worth
USD: The amount of USD the coins you own are worth

Not only does it do this for you, but I thought it was useful to see how the crypto's of interest change over time without visiting coinmarketcap. So, with a little more set-up I have a page for each crypto and how its BTC value has changed over time as well as its trade volume.

Instructions:

Create the Excel Document:

1. Create a new Excel Document and name it ColdStoragePortfolio
2. Format your table for the coins you have...
   
3. The PowerShell script will populate the BTCValue column, you set up the rest. So, in the BTC column in the E2 cell type the following then drag it down
   
4. In the BTC column in the F2 cell type the following then drag it down
   
5. Your spreadsheet should look like this, but you may have used your coins.
   
6. For each coin you are interested in, create a page with the ticker symbol and create the following header on row 1
   
7. Save the file. You are done.

Create the PowerShell script:

1. Right-click your desktop
2. Select New, then Text Document
3. Name the document PowerShellPortfolio, or whatever you would like
   
4. Open the text document, copy the code below, and paste it into the text document. If you want different tokens, feel free to add and delete tokens as you wish, but remember to make a place for them your Excel spreadsheet. Also, remember the tokens need to appear in the same order as they do in your excel spreadsheet. You will also need to find the segment of code that says "Input Excel File Path Here" and input the path to your excel file.

#The tokens
$tokens =  "bitcoin", "cardano", "ethereum"

#Creating an object that contains the web address of every API endpoint
$calls = FOREACH ($t in $tokens)
        {
     “https://api.coinmarketcap.com/v1/ticker/$t/?convert=BTC”
        }

#Making each API call, converting the JSON, and storing the results
   $webget = New-Object System.Net.WebClient
   $result = FOREACH ($i in $calls)
        {
        $webget.DownloadString($i) | ConvertFrom-Json
        }

##Instantiate the COM object
$Excel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $Excel.Workbooks.Open("Input Excel File Path Here")
$insert = 1
$col = 3

#Adding the data to the spreadsheet
   FOREACH ($r in $result)
            {
            $ExcelWorkSheet = $Excel.WorkSheets.item($r.symbol)
            $ExcelWorkSheet.activate()
            $range = $ExcelWorkSheet.UsedRange.Cells
            $row = $range.Rows.Count
            $row++
            $ExcelWorkSheet.Cells.Item($row,1) = $r.market_cap_btc
            $ExcelWorkSheet.Cells.Item($row,2) = $r."24h_volume_btc"
            $ExcelWorkSheet.Cells.Item($row,3) = $r.rank
            $ExcelWorkSheet.Cells.Item($row,4) = $r.price_btc
            $ExcelWorkSheet.Cells.item($row,4).NumberFormat="0.00000000"
            $ExcelWorkSheet.Cells.Item($row,5) = $r.last_updated
            $ExcelWorkSheet = $Excel.WorkSheets.item("Portfolio")
            $ExcelWorkSheet.activate()
        $insert++
        $ExcelWorkSheet.Cells.Item($insert,$col) = $r.price_btc
            }

$ExcelWorkSheet.Cells.Item(2, 18) = $result.price_usd[0]
$ExcelWorkBook.Save()
$ExcelWorkBook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL -Force

1. Click File... Save as... Change the file name to PowerShellPortfolio.ps1, then save
2. There should be a PowerShellPortfolio.ps1 file on your desktop now. This is the PowerShell script
3. To use the script, right-click, then select run with PowerShell

If you open your Excel spreadsheet it should now look similar to this:

The values are populated and you are done. Congratulations if this is the first programming exposure you have ever had and you made it through successfully. Now, you can use the populated information to create your pie charts and other graphs. I left that information out of the post to keep the post as short as possible.

For now on, it will only take 2 clicks to know exactly how your portfolio looks in terms of bitcoin and USD. Just right-click the powershell script you created, and click run with powershell.

Future:

If there is interest in posts like these I will continue to create new solutions that will help make life easier for crypto-investors. This is just something I threw together a few weeks ago to take care of my problem with keeping up with my cold storage portfolio. Please follow me if more solutions would interest you.