25:null 26:["$","title",null,{"children":"Microsoft Money Sunset Edition Open Financial Exchange (OFX) file for Updating Portfolio Security Prices | Mario Delgado | Observable"}] 2b:T639,md`## Prerequisites 1. Set up a dummy **Watch Account** in ***Money***: The article entitled [*Obtain stock and fund quotes after July 2013*](http://moneymvps.org/faq/article/651.aspx) details the steps with a Java-based program. The instructions on initially creating a **Watch Account** in the **Installation** section is critical to the import. The rest of the article is specific to the [Java-based](https://en.wikipedia.org/wiki/Java_(programming_language%29) program and is replaced with the OFX file created by this notebook's [JavaScript](https://en.wikipedia.org/wiki/JavaScript). 2. [Publish ](https://support.google.com/a/users/answer/9308870) a Google Sheet with security symbols and dynamic quotes using the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en). 3. Use the logic in this notebook that references a Google Sheet's [***spreadsheetId***](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id) to access the spreadsheets content as a CSV file: This notebook uses a [sample Google Sheet](https://docs.google.com/spreadsheets/d/1e53kLtCKlcAKVyEFZOi0N7ABj0XWTScY1SGMqWEb4eA) and loads the data into a [***googleData***](#googleData) variable: csvURL = *https://docs.google.com/spreadsheets/d/spreadsheetId/export?format=csv* Your custom Google Sheet, if it's formatted like this notebook's sample, can be referenced dynamically with the ***Get Google Sheet*** submit button to generate the contents of the OFX file specific to your securities' data. `2c:T477,md `Once the file has been generated, it can be downloaded to a local file and given the name ***quotes.ofx*** on your PC. The [mimetype](https://developer.mozilla.org/en-US/docs/Web/API/Blob/type) associated with download has been assigned to [application/x-msmoney](https://slick.pl/kb/htaccess/complete-list-mime-types/). If your browser ([Chrome](https://www.techjunkie.com/automatically-open-downloads-chrome/), [Edge](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_win10-msoversion_other/always-open-files-of-this-type/68910598-cc0c-4603-81ea-acb1902109f1), [FireFox](https://support.mozilla.org/en-US/kb/change-firefox-behavior-when-open-file)) is properly configured for opening an OFX file, the ***Money*** application will automatically initiate and import the file with the [Microsoft Money Import Handler](http://moneymvps.org/faq/article/407.aspx). Alternatively, you can copy/paste/save the contents of the OFX file with an editor like [***Notepad***](https://en.wikipedia.org/wiki/Microsoft_Notepad); then [import](http://moneymvps.org/faq/article/453.aspx) the newly created file into ***Money***. `2d:T7b2,function ofxFile() { // Open File Exchange specifications: https://www.ofx.net/downloads/OFX%202.2.pdf // Date format: page 89, 3.2.8.2 Date and Datetime YYYYMMDDHHMMSS GMT const strDate = new Date().toISOString().substring(0,19).replace(/T/g,"").replace(/-/g,"").replace(/:/g,"") ; // Why DTASOF is set to tomorrow: // https://github.com/hleofxquotes/hleofxquotes/tree/master/dist/misc/quotes/timezone const tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1); const strTomorrow = tomorrow.toISOString().substring(0,10).replace(/T/g,"").replace(/-/g,""); let ofx = startXML(strDate, strTomorrow); googleData.forEach(security => ofx += security[2] == "stock" ? posstock(security[0], security[1], strDate) : posmf(security[0], security[1], strDate) ); ofx += startInfo(); googleData.forEach(security => ofx += security[2] == "stock" ? stockinfo(security[0], security[1]) : mfinfo(security[0], security[1]) ); ofx += footer(); ofx = header() + formatXml(ofx); const copyXMLButton = html`` copyXMLButton.onclick = (e) => { e.preventDefault(); document.getElementById("ofxText").select(); document.execCommand("copy"); } // https://observablehq.com/@observablehq/standard-library const downloadXMLButton = DOM.download( // https://www.lifewire.com/file-extensions-and-mime-types-3469109 new Blob([ofx], {type: "application/x-msmoney"}), "quotes.ofx", "Download OFX file" ) return html`
${downloadXMLButton}${copyXMLButton}`; } 2e:T403,function startXML(strDate, strTomorrow){ const xml = ` 0 INFO Successful Sign On ${strDate} ENG 20010918083000 broker.com ${createUUID()} 0 INFO 4 ${strTomorrow} USD dummybroker.com 0123456789 `; return xml; // https://www.arungudelli.com/tutorial/javascript/how-to-create-uuid-guid-in-javascript-with-examples/ function createUUID() { return ([1e7]+-1e3+-4e3+-8e3+-1e11).replace(/[018]/g, c => (c ^ crypto.getRandomValues(new Uint8Array(1))[0] & 15 >> c / 4).toString(16) ) } } 2f:[{"id":"74ca750b29a20912573f461135ab1e9476c3fa7c807a7d237144417be4b1c116f3c45b2b9f0e0b21d92baae78bebfadaca81cc5b260e4bbfa61ae79f5be66e75","href":"https://static.observableusercontent.com/files/74ca750b29a20912573f461135ab1e9476c3fa7c807a7d237144417be4b1c116f3c45b2b9f0e0b21d92baae78bebfadaca81cc5b260e4bbfa61ae79f5be66e75","name":"MSMoneyPortfolio@1.jpg","type":"image/jpeg","lastModified":1608352217616.667,"size":158581}] 29:{"id":"c0a8555eeb5cb8f5","slug":"microsoft-money-sunset-edition-open-file-exchange-ofx-file","title":"Microsoft Money Sunset Edition Open Financial Exchange (OFX) file for Updating Portfolio Security Prices","thumbnail":"bda5b0053aa2b5cb19caee8130254ceaa175bf2e7ff4c96452b07de1c8cd3fb3","access_level":"public","create_time":"$D2020-12-18T18:38:25.821Z","update_time":"$D2026-08-28T15:53:53.397Z","view_time":null,"first_public_time":"$D2020-12-18T22:24:13.203Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"f57b9f8a19d28778","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":"starter_2024"},"version":459,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null}],"members":[{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"c7f9c81f14fa780a","slug":"google-sheet-techniques","title":"Google Sheet Techniques","owner":{"id":"f57b9f8a19d28778","login":"mariodelgadosr","name":"Mario Delgado "}},"type":"notebook","body":{"title":"Microsoft Money Sunset Edition Open Financial Exchange (OFX) file for Updating Portfolio Security Prices","cells":[{"id":0,"value":"md`# [Microsoft Money Sunset Edition](https://download.cnet.com/Microsoft-Money-Plus-Sunset-Deluxe/3000-2057_4-77545178.html) [Open Financial Exchange (OFX)](https://www.ofx.net/) [file](http://moneymvps.org/faq/article/8.aspx) for Updating Portfolio Security Prices\n![](${await FileAttachment(\"MSMoneyPortfolio@1.jpg\").url()})\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":12,"value":"toc()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"md `## JavaScript Solution Using Google Sheets\n* When Microsoft [sunsetted](https://www.microsoft.com/en-us/download/details.aspx?id=20738) the [***Money***](https://en.wikipedia.org/wiki/Microsoft_Money) personal finance application, [several alternative techniques](http://moneymvps.org/faq/article/27.aspx) were developed to keep the portfolio data updated with [OFX formatted files](http://moneymvps.org/faq/article/8.aspx) [(specification)](https://www.ofx.net/downloads/OFX%202.2.pdf). \n\n Each technique involved gathering current security price quotes from a list maintained externally to the ***Money*** application, which then updated a [***Watch Account***](http://moneymvps.org/faq/article/651.aspx) when the OFX file was imported.\n* This solution uses the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en) hosted in a [Google Sheet](https://www.google.com/sheets/about/) to data-wrangle financial security prices to create an OFX file suitable for import into ***Money***.\n\n\n\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":360,"value":"md `### Google Apps Script\n***Note***: As an alternative, the logic in this notebook is also available in a [Google Apps Script](https://github.com/MarioDelgadoSr/Google_Sheet_Script_to_OFX) that requires [installation and authorization](https://developers.google.com/apps-script/guides/services/authorization) in the Google Sheet.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":39,"value":"$2b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":60,"value":"md`## Sample Google Sheet\nThe following URL references a Google Sheet with three securities; \n1. [stock](https://en.wikipedia.org/wiki/Stock) \n2. [stock ADR](https://en.wikipedia.org/wiki/ADR)\n3. [mutual fund](https://en.wikipedia.org/wiki/Mutual_fund).\n\nIt utilizes the [GOOGLEFINANCE function](https://support.google.com/docs/answer/3093281?hl=en) to:\n* Dynamically retrieve the security's quote by referencing it's symbol in Column A and assigning it to its respective cell in Column B.\n* It then determines if the security is categorized as a stock or mutual fund; placing the results in its respective cell in Column C. \n\n|Column | Content (n=row number) |\n|-|-|\n|A|Security Ticker Symbol|\n|B|=GOOGLEFINANCE(A***n***)|\n|C|=IF(ISERROR(GOOGLEFINANCE(A***n***, \"marketcap\")), \"mutual fund\", \"stock\")|\n\nSelect an indvidual cell to see its symbol or formula.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":394,"value":"// MyDataVisualizer\nviewof googleSpreadSheetURL = textarea({\n title: \"Google Sheet's URL:\",\n value:\n \"https://docs.google.com/spreadsheets/d/1e53kLtCKlcAKVyEFZOi0N7ABj0XWTScY1SGMqWEb4eA\",\n width: \"100%\",\n rows: 1,\n submit: \"Get Google Sheet\",\n description:\n \"Reference the URL to your Google Sheet that contains securities data formatted similarly to the sample spreadsheet to generate and OFX file. Then click the 'Get Google Sheet' button.\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":68,"value":"md ``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":76,"value":"md `## Spreadsheet Data accessed via CSV file format\nThe CSV URL is determined using the [spreadsheetId](https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id):`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"// https://regexr.com/\ncsvURL = `https://docs.google.com/spreadsheets/d/${\n googleSpreadSheetURL.match(`/spreadsheets/d/([a-zA-Z0-9-_]+)`)[1]\n}/export?format=csv`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":144,"value":"md `D3's [text](https://github.com/d3/d3-fetch/blob/v3.0.1/README.md#text) and [csvParseRows](https://github.com/d3/d3-dsv/blob/v3.0.1/README.md#csvParseRows) methods are used to retrieve the data in the spreadsheet:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":83,"value":"googleData = d3.csvParseRows(await d3.text(csvURL)); ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":199,"value":"md `### Use the ***ofxFile()*** Function to Create the OFX file`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":188,"value":"ofxFile()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":191,"value":"$2c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":150,"value":"md `## Functions Used to Generate the OFX file`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":182,"value":"$2d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":179,"value":"function header(){\n \nconst headerXML = \n`OFXHEADER:100\nDATA:OFXSGML\nVERSION:102\nSECURITY:NONE\nENCODING:USASCII\nCHARSET:1252\nCOMPRESSION:NONE\nOLDFILEUID:NONE\nNEWFILEUID:NONE`;\n \nreturn headerXML; \n\n} ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":176,"value":"$2e","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":172,"value":"function posstock(strSecurity,price,strDate){\n \n const posstockXML =\n`\n\n\n${strSecurity}\nTICKER\n\nCASH\nLONG\n0\n${price}\n${price}\n${strDate}\n\n`;\n \n return posstockXML; \n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":168,"value":"function posmf(strSecurity,price,strDate){\n \n const posmfXML = \n`\n\n\n${strSecurity}\nTICKER\n\nCASH\nLONG\n0\n${price}\n${price}\n${strDate}\n\n`;\n \n return posmfXML; \n} ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":184,"value":"function startInfo(){\n \n const startInfoXML =\n`\n\n\n\n\n\n`;\n \n return startInfoXML;\n \n} ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":165,"value":"function stockinfo(strSecurity, price){\n\nconst stockinfoXML = \n`\n\n\n${strSecurity}\nTICKER\n\n${strSecurity}\n${strSecurity}\n${price}\n\n`;\n\n\treturn stockinfoXML\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":162,"value":"function mfinfo(strSecurity, price){\n\n\tconst mfinfoXML = \n`\n\n\n${strSecurity}\nTICKER\n\n${strSecurity}\n${strSecurity}\n${price}\n\nOPENEND\n`\n\n return mfinfoXML;\n}\t","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":157,"value":"function footer(){\n \n const footerXML = \n`\n\n`;\n \n return footerXML;\n \n} ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":153,"value":"// https://stackoverflow.com/a/49458964\nfunction formatXml(xml, tab) { // tab = optional indent value, default is tab (\\t)\n var formatted = '', indent= '';\n tab = tab || '\\t';\n xml.split(/>\\s*\\r\\n';\n if (node.match( /^]*[^\\/]$/ )) indent += tab; // increase indent\n });\n return formatted.substring(1, formatted.length-3);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":74,"value":"md `## Imports`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":80,"value":"d3 = require(\"d3@5\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":10,"value":"import {toc} from \"@mbostock/toc\"\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":391,"value":"import {textarea} from \"@jashkenas/inputs\"\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W2f","stdlib":"1","titling":"auto"}} 2a:"$29:body"