edited 11:30AM in Registers & Transactions (Mac) I can’t attach my spreadsheet so I will try and describe it.In Numbers on a Mac or iOS device, create a new spreadsheet with a header across the top and left side with 6 columns and N rows (plus the header row).Name the table after the currency that you are going to query.e.g.”BTC-USD”, “ATOM-USD”, etc.This is non-critical but will help you pick the right table later on.
You can have multiple tables per sheet each with a different currency.
In the top header row put Date, Open, Close, High, Low, Volume.These are the columns that Quicken’s pop up tells you (that I’ll explain in a bit).For the Dates, put the dates that you want — one per row.
e.g.I just did 12/23/2021 to 12/31/2021.In the top left data row (under Open) put: =STOCKH(“BTC-USD”, B$1, $A2) For “BTC-USD”, put the currency that you are interested it.This needs to be whatever finance.yahoo.com uses because that is where Numbers is fetching its data from.I’ve found “BTC-USD” works for bitcoin, “ATOM-USD” works for Cosmos ATOM, and “XLM-USD” works for Stellar Lumens (with their prices given in US dollars).To make sense to you in the future, the table’s name should match the currency that you put as the first argument to STOCKH.
The B$1 will pick “Open” from the header and the $A2 is the date from the date column.Now with the cell selected, hover over the right edge and a yellow dot appears in the border.Grab the dot and pull right and the other 4 data columns will fill out.With the top row data columns still selected, hover along the bottom edge of that row.The same yellow dot appears.Grab the dot and pull down to fill up the table.Select all of the data (all the table but not the header nor the Date column) and hit the Format button in the top right of the spread sheet, select the “Cell” tab, Data Format is “Number” (not currency) and the checkbox for Thousands Separator should be off.You have just completed the first table.
Rinse and repeat adding more tables for each currency you are interested in changing the first argument to STOCKH as well as the table’s name.I named my spreadsheet “Crypto Prices”.File => Export To => CSV to export this data.Pick “Create a file for each table”.
I didn’t pick the “Include table names” check box.Hit Next, pick a directory (like your Desktop) and hit Export.A directory (Folder) will be created with the name of the spreadsheet (“Crypto Prices” in my case).Inside the directory will be a file with a csv suffix for each table in the spread sheet.
You are half done!! In Quicken, create a Security for each currency…Yes…I understand these are closer to currencies than securities but I’m looking at them as investments.To create a new Security you go Window => Securities and then hit the + in the lower left corner.
You kinda have to fight with this a little bit.It helps to uncheck the “Use downloaded asset class information for this security”.Enter the Security Name (e.g.”Bitcoin”) and the symbol (e.g.”BTC-USD”) and I put “Other” for Type.It wants you to put a symbol that it recognizes but if you click away, it will accept what you put.
This confused me at first.Hit Done when you are done.You might be able to do this all in one go but I did it in two steps.Now, open the security you just created back up and click on the Price History tab.Click the Import History From CSV File.Select one of the csv files you just exported and hit Open.
It should load up the prices.
Repeat these steps for each currency.
You can now “Buy” and “Sell” these securities.The only small hitch is you have to update the price history periodically by changing the dates in the spread sheet you just created, export the csv files from Numbers, and import the csv files into Quicken.
According to posts here, Quicken needs to add some decimal numbers to what it keeps track off so you might find some subtle rounding differences between Quicken and the site / wallet that you use but this is working for me rather well right now.0.
