Ultimate Guide to Currency Conversion & Consolidation

Mike Preuss
CEO

Operating a business across many countries and dealing with multiple currencies presents plenty of unique challenges. Converting and consolidating financial data from QuickBooks, Xero and other sources should not find itself in the “challenges” category; however, it often does.

No reason to fret, the Visible team has you covered with this guide. We’ve helped many customers handle their currency conversion needs with our formula builder and Google Sheets integration but wanted to kick things up a notch with a comprehensive guide.

Transparently, we’d love for you to trial Visible & be a hopefully become a customer, but anyone will be able to find value in our currency and consolidation guide, especially for those of you using QuickBooks, Xero and/or Google Sheets! This guide will be broken down into 3 parts:

  1. Automatically creating currency exchange rates with our Google Sheet Template
  2. Combining your QuickBooks or Xero data with our formula builder to consolidate financials to one currency
  3. Charting & sharing consolidated data using Visible

Currency Exchange Rates with Google Sheets

Our first stop on our journey of currency conversion and consolidation takes us to Google Sheets. Google Sheets is great because their =googlefinance formula is able to grab exchange rates (and historical rates) for any currency.

Rather than make you work for it and end up with something like “=GOOGLEFINANCE(“Currency:”&‘Currency Conversion’!$C$3&‘Currency Conversion’!$C$4,“price”,‘Currency Conversion’!F1,‘Currency Conversion’!Q1,“Daily”)” we decided to play nice and do the work for you.

In the Google Sheet you’ll find 3 tabs. For you #lazyweb people, you can skip to the next section. For those who want to learn about the 3 tabs, keep reading. You can download the Google Sheet Template and follow along using the form below:

The first tab lets you make your selections of base currencies & the converted currencies. We’ve set it up to automate up to 5 different conversions. This is the tab you’ll be able to connect to Visible as well.

The second tab is just a simple list of countries, their currency, currency code & number. Thanks to IBAN for providing this list to us. This is the list that powers the dropdown in Column C on the first tab.

The final tab is the actual Conversion Data. This is where Google Sheets and the =googlefiance formula does its magic. This tab references your inputs from the first tab and will spit out all of the daily exchange rates for the given currencies year-to-date.

Funnily enough, Google sends us a date/timestamp that does not play nice with the =vlookup we need on the first tab, so we added a Format Date column. This Sheet will update each day with the latest rates.

Note: For the purpose of this project, we are taking the exchange rate on the final day of the month and assigning that as the exchange rate for the month. You are welcome to change the formula to be an average or a rate that you personally observed with your own bank.

p.s. if you don’t want to use Google Sheets, you can always enter in your own exchange rate data using our User Provided Metrics.

Consolidating with QuickBooks, Xero & the formula builder

The first thing we will want to do is get your financial data into Visible from QuickBooks and/or Xero. You can also upload data through Google Sheets or Excel (User Provided Data).

Head over to our knowledge base if you need any help integrating with QuickBooks or Xero. If you need any additional help you are always welcome to contact support as well.

The next thing we will want to do is get automated exchange rates from the Google Sheet we setup.

Assuming the template was not changed, the dates will be in row 1 and metrics in column E. If you made your own changes, then enter the respective column/row here.

In my example, I am going to Consolidate Revenue to USD from QuickBooks (AUD), Xero (EUR) and User Provided Data (USD). This means I’ll have 2 exchange rates created for me looking like this:

Now it is formula time. Head over to “New data source” and create formula. Your formula will look something like:

Consolidate Value = Metric (in base currency) + (Metric 2 & Exchange Rate) + (Metric 3 * Exchange Rate) etc etc. For my example it looks like this:

Hit “Save” and now we have our consolidated metric!

Charting Consolidated Financial Data with Visible

This part is the easiest and happens to be the most fun. Once you have your consolidated metrics created, you can use them in charts, tables and Updates.

These charts will always be up to date with your data syncing from Sheets, QuickBooks and Xero every night. If you want to level up your Consolidation Reports, check out our Variance Reporting module to generate your Month-to-Date and Year-to-Date variance reports.

We hope you found some value with this guide and our Google Sheet template. If you need any additional Visible help or have any questions, you can contact us here.

Up & to the right,

-Mike & The Visible Team

You may also enjoy:
Product Updates
Shaping the Future: Unveiling Our Brand Refresh
Visible started as a way for founders to update their investors but has since transformed into a purpose-built platform for both founders and investors. Since 2014, we’ve powered over 3M interactions between founders and investors. Our brand refresh represents our commitment to equip founders and the investors who back them. Founders can now manage their investor relationships and capital raises from start to finish. Investors can centralize data, uncover insights, and report to their LPs and stakeholders—all from one place. We are excited to unveil our refreshed brand, marking the beginning of the next decade in our dedication and commitment to founders and investors. Equipping Founders and Investors for Growth Raising capital is fundamentally about building relationships. Founders and investors spend months forging connections that, once solidified by a term sheet, can evolve into partnerships lasting 8 to 10 years and beyond. We set out to highlight the people behind the investments, not just the companies or firms they represent. As we continue to grow, you'll notice more familiar faces from different startups and venture funds featured on our site (👋 Hi Lacey!). Shaping Our Future Across our website and marketing assets, you’ll see 3D shapes floating in the background. While they add aesthetic appeal, they also carry a deeper meaning. For example: We believe raising capital from VCs or LPs closely resembles a fundraising funnel, a concept brought to life through the funnel shape that appears across various pages and assets. Sphere, torus, and pill shapes symbolize the connections between VCs, founders, and stakeholders, reflecting the interwoven nature of these relationships. These forms also symbolize forward momentum and the continuous progression within the venture ecosystem. As our product has evolved to offer greater flexibility in moving data into and out of Visible, you'll notice pipes integrated into the design, visually representing the seamless flow of data through our platform. Logo and Wordmark To maintain and perpetuate our brand equity, we opted to anchor our brand to our current logo and wordmark. The Visible logomark is made from three overlapping, equilateral triangles. Each triangle is slightly transparent, allowing the mark to interact with other design elements. These triangles represent human relationships and the connection between founders and investors, which is core to our mission and vision for our company. Color Palette Our signature black primary color remains, but we've refined our palette for a fresh look. We've transitioned from a complex, rich color scheme to a more cohesive and defined palette, creating a unified and bold approach to color across our brand. Introducing the new Visible color palette: Product Guided by our core values of trust and transparency, we designed our product to embody these principles. Our product visuals across the site are predominantly high-fidelity for the first time, highlighting its capabilities visually rather than relying solely on words. The updated typography and refined color palette are seamlessly integrated into our product screens, further enhancing the overall brand experience. Voice & Tone A brand is more than just its visual identity; it requires a voice and tone that harmonizes with its design. We aimed for our voice to be authentic, credible, and consistent, supporting both investors and founders alike. This commitment led us to define key principles for our voice and tone: Be a guide Less is more Recognize that different users face different challenges As you explore our marketing site, we hope Visible reflects these values and resonates with you. Transparency = Trust As we noted earlier, raising capital is fundamentally about relationships. Trust and transparency form the foundation of any strong connection. Throughout our website, you'll notice subtle transparency elements that embody this core principle. This new iteration of our brand is built to scale, but that doesn’t mean we won’t continue to evolve and elevate the experience for the betterment of our prospects and customers. Here's to shaping the next 10 years! Up and to the right,  The Visible Team
Product Updates
Product Update: Turn Emails Into Insights With Visible AI Inbox
Customer Stories
Case Study: Airtree Venture's Transformation with Visible
Fundraising
9 Tips for Effective Investor Networking